3928 lines
246 KiB
SQL
3928 lines
246 KiB
SQL
# 看注释执行！！！！！！
|
||
# 2024-01-08 总共四个sql文件需要执行，先执行这个！！！
|
||
|
||
# 本sql文件无法直接执行，文件内容较多，建议在IntelliJ IDEA开发工具打开，右键选择 Folding-->Collapse All 方便查看并且复制
|
||
# 本sql文件默认ip为192.168.193.128，按需替换成自己本机ip
|
||
|
||
# 整体流程：将原本订单退款、采购订单改为8库64表，需要将原数据都统计到总表中，再建新分表，再通过sql分配到各个分表中，最后删除总表数据
|
||
|
||
# 按顺序执行！，执行时只按分块执行，例如第一步执行 1.在总表新建表，就只执行到 CREATE TABLE purchase_order_addr，也即 2.统计订单表数据到总表、清空分库信息 之前，
|
||
# 确保执行成功后再执行下一步2，以此类推下述每一步
|
||
|
||
# ============================== 1.在总表新建表 ==============================
|
||
USE tmerclub_order;
|
||
DROP TABLE IF EXISTS `order_refund`;
|
||
CREATE TABLE order_refund (
|
||
`refund_id` BIGINT NOT NULL COMMENT '记录ID',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` BIGINT NOT NULL COMMENT '店铺ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '买家ID',
|
||
`order_id` BIGINT NOT NULL COMMENT '订单号',
|
||
`order_item_id` BIGINT NOT NULL DEFAULT '0' COMMENT '订单项ID(0:为全部订单项)',
|
||
`refund_count` INT DEFAULT '0' COMMENT '退货数量(0:为全部订单项)',
|
||
`refund_score` BIGINT DEFAULT NULL COMMENT '退还积分',
|
||
`refund_amount` BIGINT DEFAULT NULL COMMENT '退款金额',
|
||
`platform_refund_commission` BIGINT DEFAULT NULL COMMENT '平台佣金退款金额',
|
||
`platform_refund_amount` BIGINT DEFAULT NULL COMMENT '平台退款金额（退款时将这部分钱退回给平台，所以商家要扣除从平台这里获取的金额）',
|
||
`distribution_total_amount` BIGINT DEFAULT NULL COMMENT '退款单总分销金额',
|
||
`refund_type` TINYINT NOT NULL DEFAULT '0' COMMENT '退款单类型（1:整单退款,2:单个物品退款）',
|
||
`apply_type` TINYINT NOT NULL COMMENT '申请类型:1,仅退款,2退款退货',
|
||
`pay_sys_type` TINYINT NOT NULL DEFAULT '0' COMMENT '支付系统类型 0默认1通联支付',
|
||
`is_received` TINYINT DEFAULT NULL COMMENT '是否接收到商品(1:已收到,0:未收到)',
|
||
`close_type` TINYINT DEFAULT NULL COMMENT '退款关闭原因(1.买家撤销退款 2.卖家拒绝退款 3.退款申请超时被系统关闭)',
|
||
`return_money_sts` TINYINT NOT NULL DEFAULT '0' COMMENT '处理退款状态:(1.买家申请 2.卖家接受 3.买家发货 4.卖家收货 5.退款成功 -1.退款关闭)详情见ReturnMoneyStsType',
|
||
`buyer_reason` TINYINT DEFAULT NULL COMMENT '申请原因(具体见BuyerReasonType)',
|
||
`buyer_desc` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请说明',
|
||
`buyer_mobile` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系方式（退款时留下的手机号码）',
|
||
`img_urls` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
`shop_img_urls` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商家/供应商文件凭证(逗号隔开)',
|
||
`over_time` DATETIME DEFAULT NULL COMMENT '超时时间（超过该时间不处理，系统将自动处理）（保留字段）',
|
||
`reject_message` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '拒绝原因',
|
||
`seller_msg` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卖家备注',
|
||
`shop_seller_msg` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商家备注(移交供应商处理时保存)',
|
||
`handel_time` DATETIME DEFAULT NULL COMMENT '受理时间',
|
||
`supplier_time` DATETIME DEFAULT NULL COMMENT '移交供应商时间',
|
||
`delivery_time` DATETIME DEFAULT NULL COMMENT '发货时间',
|
||
`receive_time` DATETIME DEFAULT NULL COMMENT '收货时间',
|
||
`close_time` DATETIME DEFAULT NULL COMMENT '关闭时间',
|
||
`decision_time` DATETIME DEFAULT NULL COMMENT '确定时间(确定退款时间)',
|
||
`refund_time` DATETIME DEFAULT NULL COMMENT '退款时间',
|
||
`supplier_id` BIGINT NOT NULL DEFAULT '0' COMMENT '供应商id',
|
||
`supplier_handle_status` TINYINT DEFAULT '-1' COMMENT '供应商处理状态， -1.商家自行处理 0.待供应商处理 1.供应商已同意 2.供应商已拒绝',
|
||
`purchase_refund_amount` BIGINT DEFAULT '0' COMMENT '采购退款金额',
|
||
`pur_platform_refund_commission` BIGINT DEFAULT '0' COMMENT '采购的平台佣金退款金额',
|
||
`return_giveaway_ids` VARCHAR(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款退回的赠品订单项ids',
|
||
`refund_actual_total` DECIMAL(15,2) DEFAULT NULL COMMENT '当前退款的实付金额（实付金额减去不退回赠品的金额）',
|
||
`platform_intervention_status` TINYINT NOT NULL DEFAULT '-1' COMMENT '平台介入状态 -1.没有介入 1.用户申请介入 2.平台同意介入 3.平台拒绝介入 5.平台同意退款成功',
|
||
`apply_intervention_reason` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户申请介入理由',
|
||
`apply_intervention_img_urls` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请介入的文件凭证(逗号隔开)',
|
||
`intervention_refund_type` TINYINT DEFAULT NULL COMMENT '平台介入退款方式 1.商家承担 2.商家供应商承担（供应商发货订单） 3.不同意退款',
|
||
`after_intervention_refund_amount` BIGINT DEFAULT NULL COMMENT '平台介入前的退款金额',
|
||
`platform_message` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '平台留言',
|
||
`apply_intervention_time` DATETIME DEFAULT NULL COMMENT '申请介入时间',
|
||
`intervention_finish_time` DATETIME DEFAULT NULL COMMENT '介入完成时间',
|
||
PRIMARY KEY (`refund_id`)
|
||
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款记录信息';
|
||
|
||
DROP TABLE IF EXISTS `order_refund_intervention`;
|
||
CREATE TABLE order_refund_intervention (
|
||
`refund_intervertion_id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`biz_id` bigint DEFAULT '0' COMMENT '商家/供应商/用户id',
|
||
`sys_type` tinyint DEFAULT NULL COMMENT '系统类型，0.普通用户 1.商家端 2.平台端 3.供应商端',
|
||
`voucher_desc` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '凭证说明',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
PRIMARY KEY (`refund_intervertion_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款介入记录';
|
||
|
||
DROP TABLE IF EXISTS `order_refund_addr`;
|
||
CREATE TABLE order_refund_addr (
|
||
`refund_addr_id` bigint NOT NULL AUTO_INCREMENT COMMENT '物流ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款号',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`delivery_company_id` bigint DEFAULT NULL COMMENT '物流公司ID',
|
||
`delivery_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流公司名称',
|
||
`delivery_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流单号',
|
||
`consignee_name` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人姓名',
|
||
`consignee_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人电话（顺丰快递需要）',
|
||
`consignee_telephone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人座机',
|
||
`consignee_post_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人邮政编码',
|
||
`consignee_addr` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人地址',
|
||
`sender_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发送人手机号码',
|
||
`sender_remarks` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '买家备注',
|
||
`imgs` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片凭证',
|
||
PRIMARY KEY (`refund_addr_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户退货物流地址';
|
||
|
||
DROP TABLE IF EXISTS `order_refund_settlement`;
|
||
CREATE TABLE order_refund_settlement (
|
||
`settlement_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '退款结算单据id',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`pay_id` bigint DEFAULT NULL COMMENT '订单支付单号',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`refund_id` bigint DEFAULT NULL COMMENT '退款单编号（本系统退款单号）',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`biz_refund_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信/支付宝退款单号（支付平台退款单号）',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式(1:微信支付 2支付宝支付)',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '退款状态(1:申请中 2：已完成 -1失败)',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`order_total_amount` bigint DEFAULT NULL COMMENT '订单总额',
|
||
`version` int DEFAULT '0' COMMENT '版本号',
|
||
PRIMARY KEY (`settlement_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='退款支付结算单据';
|
||
|
||
DROP TABLE IF EXISTS `purchase_order`;
|
||
CREATE TABLE purchase_order (
|
||
`purchase_order_id` bigint NOT NULL AUTO_INCREMENT COMMENT '采购id',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deliver_time` datetime DEFAULT NULL COMMENT '送达时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`supplier_id` bigint NOT NULL COMMENT '供应商id',
|
||
`total_amount` bigint NOT NULL COMMENT '总采购金额',
|
||
`total_stock` int NOT NULL COMMENT '总采购库存数量',
|
||
`actual_total_stock` int DEFAULT NULL COMMENT '实际总库存数量',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
|
||
`employee_id` bigint DEFAULT NULL COMMENT '操作员工id',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '配送类型 1:快递 2:自提 3：无需快递 4同城配送',
|
||
`purchase_order_addr_id` bigint DEFAULT NULL COMMENT '采购订单地址Id',
|
||
`voucher_img_urls` VARCHAR(1000) DEFAULT NULL COMMENT '付款凭证图片 多个图片逗号分隔',
|
||
`warehouse_id` bigint DEFAULT NULL COMMENT '仓库id',
|
||
`supplier_warehouse_id` bigint DEFAULT NULL COMMENT '供应商仓库id',
|
||
`stock_point_type` int DEFAULT NULL COMMENT '库存点类型(1:仓库, 2:门店)',
|
||
PRIMARY KEY (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单';
|
||
|
||
DROP TABLE IF EXISTS `purchase_order_lang`;
|
||
CREATE TABLE purchase_order_lang (
|
||
`purchase_order_id` bigint NOT NULL,
|
||
`lang` tinyint NOT NULL,
|
||
`spu_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
|
||
PRIMARY KEY (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单-国际化';
|
||
|
||
DROP TABLE IF EXISTS `purchase_order_item`;
|
||
CREATE TABLE purchase_order_item (
|
||
`purchase_item_id` bigint NOT NULL COMMENT 'id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`purchase_order_id` bigint DEFAULT NULL COMMENT '采购订单id',
|
||
`spu_id` bigint DEFAULT NULL COMMENT '商品id',
|
||
`sku_id` bigint DEFAULT NULL COMMENT 'sku id',
|
||
`spu_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品编码',
|
||
`party_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku编码',
|
||
`pic` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品图片',
|
||
`purchase_amount` bigint DEFAULT NULL COMMENT '采购金额',
|
||
`purchase_price` bigint DEFAULT NULL COMMENT '采购价',
|
||
`purchase_stock` int DEFAULT NULL COMMENT '采购库存数量',
|
||
`actual_stock` int DEFAULT NULL COMMENT '实际库存数量',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '订单项退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`be_delivered_num` int DEFAULT NULL COMMENT '0全部发货 其他数量为剩余待发货数量',
|
||
PRIMARY KEY (`purchase_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单项';
|
||
|
||
DROP TABLE IF EXISTS `purchase_order_item_lang`;
|
||
CREATE TABLE purchase_order_item_lang (
|
||
`purchase_item_id` bigint NOT NULL COMMENT '订单项ID',
|
||
`lang` tinyint NOT NULL COMMENT '语言 1.中文 2.英文',
|
||
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
PRIMARY KEY (`purchase_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项-国际化';
|
||
|
||
DROP TABLE IF EXISTS `purchase_order_addr`;
|
||
CREATE TABLE purchase_order_addr (
|
||
`purchase_order_addr_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`consignee` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收货人',
|
||
`province_id` bigint DEFAULT NULL COMMENT '省ID',
|
||
`province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省',
|
||
`city_id` bigint DEFAULT NULL COMMENT '城市ID',
|
||
`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
|
||
`area_id` bigint DEFAULT NULL COMMENT '区域ID',
|
||
`area` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
|
||
`addr` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
|
||
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机',
|
||
PRIMARY KEY (`purchase_order_addr_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单配送地址';
|
||
|
||
|
||
# ============================== 2.统计订单表数据到总表、清空分库信息 ==============================
|
||
# 假如执行报错Column 'xxx(字段名)' cannot be null，检查下分库中表的数据结构，顺序是否与上述建表接口一致，不一致就将上述建表结构调整到和分库分表中一致
|
||
|
||
# 再重新执行一次,后续新建的分表结构也要和总库中总表结构一致，否则会导入失败，要确保所有分表的结构一致，否则会出现难以预料的字段赋值错误bug！！！！
|
||
|
||
# 为了防止sql执行报错，目前只编写了0库sql，0库执行正常后，剩下六个库执行的时候只需要将首句USE tmerclub_release_order_0;中的0改成1，执行后改成2...以此类推直到执行到7库完成
|
||
USE tmerclub_order_0;
|
||
# 新增调拨订单表、采购订单字段补充
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS addAllotOrderTable //
|
||
CREATE PROCEDURE addAllotOrderTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i < 2 DO
|
||
|
||
SET @update_purchase_order = CONCAT( "ALTER TABLE `purchase_order_", i, "`
|
||
ADD COLUMN `warehouse_id` bigint(0) NULL COMMENT '仓库id' AFTER `voucher_img_urls`,
|
||
ADD COLUMN `supplier_warehouse_id` bigint(0) NULL COMMENT '供应商仓库id' AFTER `warehouse_id`,
|
||
ADD COLUMN `stock_point_type` int(1) NULL COMMENT '库存点类型(1:仓库, 2:门店)' AFTER `supplier_warehouse_id`;
|
||
" );
|
||
PREPARE update_purchase_order FROM @update_purchase_order;
|
||
EXECUTE update_purchase_order;
|
||
|
||
SET i = i + 1;
|
||
END WHILE;
|
||
END //
|
||
CALL addAllotOrderTable() //
|
||
DROP PROCEDURE addAllotOrderTable //
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_0;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<32 DO
|
||
# 汇总到总表
|
||
SET @create_order_refund_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_refund`
|
||
(SELECT * FROM order_refund_', i, ")"
|
||
);
|
||
PREPARE create_order_refund_table FROM @create_order_refund_table;
|
||
EXECUTE create_order_refund_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_refund_intervention` (`refund_id`,`create_time`,`update_time`,`biz_id`,`sys_type`,`voucher_desc`,`img_urls`)
|
||
(SELECT `refund_id`,`create_time`,`update_time`,`biz_id`,`sys_type`,`voucher_desc`,`img_urls` FROM order_refund_intervention_', i, ")"
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_refund_addr` (`create_time`,`update_time`,`shop_id`,`refund_id`,`user_id`,`delivery_company_id`,`delivery_name`,`delivery_no`,`consignee_name`,`consignee_mobile`,`consignee_telephone`,`consignee_post_code`,`consignee_addr`,`sender_mobile`,`sender_remarks`,`imgs`)
|
||
(SELECT `create_time`,`update_time`,`shop_id`,`refund_id`,`user_id`,`delivery_company_id`,`delivery_name`,`delivery_no`,`consignee_name`,`consignee_mobile`,`consignee_telephone`,`consignee_post_code`,`consignee_addr`,`sender_mobile`,`sender_remarks`,`imgs` FROM order_refund_addr_', i, ")"
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`order_refund_settlement` (`create_time`,`update_time`,`pay_id`,`order_id`,`refund_id`,`user_id`,`biz_refund_no`,`pay_type`,`refund_status`,`refund_amount`,`order_total_amount`,`version`)
|
||
(SELECT `create_time`,`update_time`,`pay_id`,`order_id`,`refund_id`,`user_id`,`biz_refund_no`,`pay_type`,`refund_status`,`refund_amount`,`order_total_amount`,`version` FROM order_refund_settlement_', i, ")"
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
# 清理分库
|
||
SET @clear_refund_table = CONCAT(
|
||
'DELETE FROM order_refund_', i
|
||
);
|
||
PREPARE clear_refund_table FROM @clear_refund_table;
|
||
EXECUTE clear_refund_table;
|
||
|
||
SET @clear_order_refund_intervention_table = CONCAT(
|
||
'DELETE FROM order_refund_intervention_', i
|
||
);
|
||
PREPARE clear_order_refund_intervention_table FROM @clear_order_refund_intervention_table;
|
||
EXECUTE clear_order_refund_intervention_table;
|
||
|
||
SET @clear_order_refund_addr_table = CONCAT(
|
||
'DELETE FROM order_refund_addr_', i
|
||
);
|
||
PREPARE clear_order_refund_addr_table FROM @clear_order_refund_addr_table;
|
||
EXECUTE clear_order_refund_addr_table;
|
||
|
||
SET @clear_order_refund_settlement_table = CONCAT(
|
||
'DELETE FROM order_refund_settlement_', i
|
||
);
|
||
PREPARE clear_order_refund_settlement_table FROM @clear_order_refund_settlement_table;
|
||
EXECUTE clear_order_refund_settlement_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
|
||
SET i = 0;
|
||
WHILE i<2 DO
|
||
# 汇总到总表
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`purchase_order`
|
||
(SELECT * FROM purchase_order_', i, ")"
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @delete_purchase_order_lang_lang_table = CONCAT(
|
||
'delete from purchase_order_lang_', i, " WHERE lang = 2"
|
||
);
|
||
PREPARE delete_purchase_order_lang_lang_table FROM @delete_purchase_order_lang_lang_table;
|
||
EXECUTE delete_purchase_order_lang_lang_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`purchase_order_lang`
|
||
(SELECT * FROM purchase_order_lang_', i, ")"
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`purchase_order_item`
|
||
(SELECT * FROM purchase_order_item_', i, ")"
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @delete_purchase_order_item_lang_lang_table = CONCAT(
|
||
'delete from purchase_order_item_lang_', i, " WHERE lang = 2"
|
||
);
|
||
PREPARE delete_purchase_order_item_lang_lang_table FROM @delete_purchase_order_item_lang_lang_table;
|
||
EXECUTE delete_purchase_order_item_lang_lang_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`purchase_order_item_lang`
|
||
(SELECT * FROM purchase_order_item_lang_', i, ")"
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'INSERT INTO tmerclub_order.`purchase_order_addr`
|
||
(SELECT * FROM purchase_order_addr_', i, ")"
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
# 清理分库
|
||
SET @clear_purchase_order_table = CONCAT(
|
||
'DELETE FROM purchase_order_', i
|
||
);
|
||
PREPARE clear_purchase_order_table FROM @clear_purchase_order_table;
|
||
EXECUTE clear_purchase_order_table;
|
||
|
||
SET @clear_purchase_order_lang_table = CONCAT(
|
||
'DELETE FROM purchase_order_lang_', i
|
||
);
|
||
PREPARE clear_purchase_order_lang_table FROM @clear_purchase_order_lang_table;
|
||
EXECUTE clear_purchase_order_lang_table;
|
||
|
||
SET @purchase_order_item_ = CONCAT(
|
||
'DELETE FROM purchase_order_item_', i
|
||
);
|
||
PREPARE purchase_order_item_ FROM @purchase_order_item_;
|
||
EXECUTE purchase_order_item_;
|
||
|
||
SET @clear_purchase_order_item_lang_table = CONCAT(
|
||
'DELETE FROM purchase_order_item_lang_', i
|
||
);
|
||
PREPARE clear_purchase_order_item_lang_table FROM @clear_purchase_order_item_lang_table;
|
||
EXECUTE clear_purchase_order_item_lang_table;
|
||
|
||
SET @clear_purchase_order_addr_table = CONCAT(
|
||
'DELETE FROM purchase_order_addr_', i
|
||
);
|
||
PREPARE clear_purchase_order_addr_table FROM @clear_purchase_order_addr_table;
|
||
EXECUTE clear_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
|
||
# ============================== 3.新建订单分表 ==============================
|
||
# 请确保上述1-2执行成功后再执行此sql
|
||
# !!!注意，如果步骤2中曾经修改过总表结构，请检查下述建表结构是否与原分表结构一致，不一致请调整到和总表以及原有分表一致
|
||
USE tmerclub_order_0;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
# ============================== 订单退款 ==============================
|
||
SET i = 32;
|
||
WHILE i<64 DO
|
||
SET @create_order_refund_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_', i,
|
||
"
|
||
(
|
||
`refund_id` bigint NOT NULL COMMENT '记录ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint NOT NULL COMMENT '店铺ID',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`order_id` bigint NOT NULL COMMENT '订单号',
|
||
`order_item_id` bigint NOT NULL DEFAULT '0' COMMENT '订单项ID(0:为全部订单项)',
|
||
`refund_count` int DEFAULT '0' COMMENT '退货数量(0:为全部订单项)',
|
||
`refund_score` bigint DEFAULT NULL COMMENT '退还积分',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`platform_refund_commission` bigint DEFAULT NULL COMMENT '平台佣金退款金额',
|
||
`platform_refund_amount` bigint DEFAULT NULL COMMENT '平台退款金额（退款时将这部分钱退回给平台，所以商家要扣除从平台这里获取的金额）',
|
||
`distribution_total_amount` bigint DEFAULT NULL COMMENT '退款单总分销金额',
|
||
`refund_type` tinyint NOT NULL DEFAULT '0' COMMENT '退款单类型（1:整单退款,2:单个物品退款）',
|
||
`apply_type` tinyint NOT NULL COMMENT '申请类型:1,仅退款,2退款退货',
|
||
`pay_sys_type` tinyint NOT NULL DEFAULT '0' COMMENT '支付系统类型 0默认1通联支付',
|
||
`is_received` tinyint DEFAULT NULL COMMENT '是否接收到商品(1:已收到,0:未收到)',
|
||
`close_type` tinyint DEFAULT NULL COMMENT '退款关闭原因(1.买家撤销退款 2.卖家拒绝退款 3.退款申请超时被系统关闭)',
|
||
`return_money_sts` tinyint NOT NULL DEFAULT '0' COMMENT '处理退款状态:(1.买家申请 2.卖家接受 3.买家发货 4.卖家收货 5.退款成功 -1.退款关闭)详情见ReturnMoneyStsType',
|
||
`buyer_reason` tinyint DEFAULT NULL COMMENT '申请原因(具体见BuyerReasonType)',
|
||
`buyer_desc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请说明',
|
||
`buyer_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系方式（退款时留下的手机号码）',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
`shop_img_urls` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ''商家/供应商文件凭证(逗号隔开)'',
|
||
`over_time` datetime DEFAULT NULL COMMENT '超时时间（超过该时间不处理，系统将自动处理）（保留字段）',
|
||
`reject_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '拒绝原因',
|
||
`seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卖家备注',
|
||
`shop_seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商家备注(移交供应商处理时保存)',
|
||
`handel_time` datetime DEFAULT NULL COMMENT '受理时间',
|
||
`supplier_time` datetime DEFAULT NULL COMMENT '移交供应商时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`receive_time` datetime DEFAULT NULL COMMENT '收货时间',
|
||
`close_time` datetime DEFAULT NULL COMMENT '关闭时间',
|
||
`decision_time` datetime DEFAULT NULL COMMENT '确定时间(确定退款时间)',
|
||
`refund_time` datetime DEFAULT NULL COMMENT '退款时间',
|
||
`supplier_id` bigint NOT NULL DEFAULT '0' COMMENT '供应商id',
|
||
`supplier_handle_status` tinyint DEFAULT '-1' COMMENT '供应商处理状态， -1.商家自行处理 0.待供应商处理 1.供应商已同意 2.供应商已拒绝',
|
||
`purchase_refund_amount` bigint DEFAULT '0' COMMENT '采购退款金额',
|
||
`pur_platform_refund_commission` bigint DEFAULT '0' COMMENT '采购的平台佣金退款金额',
|
||
`return_giveaway_ids` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款退回的赠品订单项ids',
|
||
`refund_actual_total` decimal(15,2) DEFAULT NULL COMMENT '当前退款的实付金额（实付金额减去不退回赠品的金额）',
|
||
`platform_intervention_status` tinyint NOT NULL DEFAULT '-1' COMMENT '平台介入状态 -1.没有介入 1.用户申请介入 2.平台同意介入 3.平台拒绝介入 5.平台同意退款成功',
|
||
`apply_intervention_reason` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户申请介入理由',
|
||
`apply_intervention_img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请介入的文件凭证(逗号隔开)',
|
||
`intervention_refund_type` tinyint DEFAULT NULL COMMENT '平台介入退款方式 1.商家承担 2.商家供应商承担（供应商发货订单） 3.不同意退款',
|
||
`after_intervention_refund_amount` bigint DEFAULT NULL COMMENT '平台介入前的退款金额',
|
||
`platform_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '平台留言',
|
||
`apply_intervention_time` datetime DEFAULT NULL COMMENT '申请介入时间',
|
||
`intervention_finish_time` datetime DEFAULT NULL COMMENT '介入完成时间',
|
||
PRIMARY KEY (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_item_id` (`order_item_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款记录信息'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_table FROM @create_order_refund_table;
|
||
EXECUTE create_order_refund_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_intervention_', i,
|
||
"
|
||
(
|
||
`refund_intervertion_id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`biz_id` bigint DEFAULT '0' COMMENT '商家/供应商/用户id',
|
||
`sys_type` tinyint DEFAULT NULL COMMENT '系统类型，0.普通用户 1.商家端 2.平台端 3.供应商端',
|
||
`voucher_desc` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '凭证说明',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
PRIMARY KEY (`refund_intervertion_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_biz_id` (`biz_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款介入记录'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_addr_', i,
|
||
"
|
||
(
|
||
`refund_addr_id` bigint NOT NULL AUTO_INCREMENT COMMENT '物流ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款号',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`delivery_company_id` bigint DEFAULT NULL COMMENT '物流公司ID',
|
||
`delivery_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流公司名称',
|
||
`delivery_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流单号',
|
||
`consignee_name` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人姓名',
|
||
`consignee_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人电话（顺丰快递需要）',
|
||
`consignee_telephone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人座机',
|
||
`consignee_post_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人邮政编码',
|
||
`consignee_addr` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人地址',
|
||
`sender_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发送人手机号码',
|
||
`sender_remarks` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '买家备注',
|
||
`imgs` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片凭证',
|
||
PRIMARY KEY (`refund_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_delivery_company_id` (`delivery_company_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户退货物流地址'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_settlement_', i,
|
||
"
|
||
(
|
||
`settlement_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '退款结算单据id',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`pay_id` bigint DEFAULT NULL COMMENT '订单支付单号',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`refund_id` bigint DEFAULT NULL COMMENT '退款单编号（本系统退款单号）',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`biz_refund_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信/支付宝退款单号（支付平台退款单号）',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式(1:微信支付 2支付宝支付)',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '退款状态(1:申请中 2：已完成 -1失败)',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`order_total_amount` bigint DEFAULT NULL COMMENT '订单总额',
|
||
`version` int DEFAULT '0' COMMENT '版本号',
|
||
PRIMARY KEY (`settlement_id`),
|
||
UNIQUE KEY `uni_refund_id` (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_pay_id` (`pay_id`),
|
||
KEY `idx_biz_refund_no` (`biz_refund_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='退款支付结算单据'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
# ============================== 采购订单 ==============================
|
||
SET i = 2;
|
||
WHILE i<64 DO
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_', i,
|
||
"(`purchase_order_id` bigint NOT NULL AUTO_INCREMENT COMMENT '采购id',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deliver_time` datetime DEFAULT NULL COMMENT '送达时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`supplier_id` bigint NOT NULL COMMENT '供应商id',
|
||
`total_amount` bigint NOT NULL COMMENT '总采购金额',
|
||
`total_stock` int NOT NULL COMMENT '总采购库存数量',
|
||
`actual_total_stock` int DEFAULT NULL COMMENT '实际总库存数量',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
|
||
`employee_id` bigint DEFAULT NULL COMMENT '操作员工id',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '配送类型 1:快递 2:自提 3：无需快递 4同城配送',
|
||
`purchase_order_addr_id` bigint DEFAULT NULL COMMENT '采购订单地址Id',
|
||
`voucher_img_urls` VARCHAR(1000) DEFAULT NULL COMMENT '付款凭证图片 多个图片逗号分隔',
|
||
`warehouse_id` bigint DEFAULT NULL COMMENT '仓库id',
|
||
`supplier_warehouse_id` bigint DEFAULT NULL COMMENT '供应商仓库id',
|
||
`stock_point_type` int DEFAULT NULL COMMENT '库存点类型(1:仓库, 2:门店)',
|
||
PRIMARY KEY (`purchase_order_id`),
|
||
KEY `idx_supplier_id` (`supplier_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_lang_', i,
|
||
"
|
||
(
|
||
`purchase_order_id` bigint NOT NULL,
|
||
`lang` tinyint NOT NULL,
|
||
`spu_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
|
||
PRIMARY KEY (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT 'id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`purchase_order_id` bigint DEFAULT NULL COMMENT '采购订单id',
|
||
`spu_id` bigint DEFAULT NULL COMMENT '商品id',
|
||
`sku_id` bigint DEFAULT NULL COMMENT 'sku id',
|
||
`spu_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品编码',
|
||
`party_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku编码',
|
||
`pic` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品图片',
|
||
`purchase_amount` bigint DEFAULT NULL COMMENT '采购金额',
|
||
`purchase_price` bigint DEFAULT NULL COMMENT '采购价',
|
||
`purchase_stock` int DEFAULT NULL COMMENT '采购库存数量',
|
||
`actual_stock` int DEFAULT NULL COMMENT '实际库存数量',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '订单项退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`be_delivered_num` int DEFAULT NULL COMMENT '0全部发货 其他数量为剩余待发货数量',
|
||
PRIMARY KEY (`purchase_item_id`),
|
||
KEY `idx_purchase_order_id` (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单项'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_lang_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT '订单项ID',
|
||
`lang` tinyint NOT NULL COMMENT '语言 1.中文 2.英文',
|
||
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
PRIMARY KEY (`purchase_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_addr_', i,
|
||
"
|
||
(
|
||
`purchase_order_addr_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`consignee` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收货人',
|
||
`province_id` bigint DEFAULT NULL COMMENT '省ID',
|
||
`province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省',
|
||
`city_id` bigint DEFAULT NULL COMMENT '城市ID',
|
||
`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
|
||
`area_id` bigint DEFAULT NULL COMMENT '区域ID',
|
||
`area` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
|
||
`addr` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
|
||
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机',
|
||
PRIMARY KEY (`purchase_order_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单配送地址'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_1;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
# ============================== 订单退款 ==============================
|
||
SET i = 32;
|
||
WHILE i<64 DO
|
||
SET @create_order_refund_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_', i,
|
||
"
|
||
(
|
||
`refund_id` bigint NOT NULL COMMENT '记录ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint NOT NULL COMMENT '店铺ID',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`order_id` bigint NOT NULL COMMENT '订单号',
|
||
`order_item_id` bigint NOT NULL DEFAULT '0' COMMENT '订单项ID(0:为全部订单项)',
|
||
`refund_count` int DEFAULT '0' COMMENT '退货数量(0:为全部订单项)',
|
||
`refund_score` bigint DEFAULT NULL COMMENT '退还积分',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`platform_refund_commission` bigint DEFAULT NULL COMMENT '平台佣金退款金额',
|
||
`platform_refund_amount` bigint DEFAULT NULL COMMENT '平台退款金额（退款时将这部分钱退回给平台，所以商家要扣除从平台这里获取的金额）',
|
||
`distribution_total_amount` bigint DEFAULT NULL COMMENT '退款单总分销金额',
|
||
`refund_type` tinyint NOT NULL DEFAULT '0' COMMENT '退款单类型（1:整单退款,2:单个物品退款）',
|
||
`apply_type` tinyint NOT NULL COMMENT '申请类型:1,仅退款,2退款退货',
|
||
`pay_sys_type` tinyint NOT NULL DEFAULT '0' COMMENT '支付系统类型 0默认1通联支付',
|
||
`is_received` tinyint DEFAULT NULL COMMENT '是否接收到商品(1:已收到,0:未收到)',
|
||
`close_type` tinyint DEFAULT NULL COMMENT '退款关闭原因(1.买家撤销退款 2.卖家拒绝退款 3.退款申请超时被系统关闭)',
|
||
`return_money_sts` tinyint NOT NULL DEFAULT '0' COMMENT '处理退款状态:(1.买家申请 2.卖家接受 3.买家发货 4.卖家收货 5.退款成功 -1.退款关闭)详情见ReturnMoneyStsType',
|
||
`buyer_reason` tinyint DEFAULT NULL COMMENT '申请原因(具体见BuyerReasonType)',
|
||
`buyer_desc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请说明',
|
||
`buyer_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系方式（退款时留下的手机号码）',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
`shop_img_urls` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ''商家/供应商文件凭证(逗号隔开)'',
|
||
`over_time` datetime DEFAULT NULL COMMENT '超时时间（超过该时间不处理，系统将自动处理）（保留字段）',
|
||
`reject_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '拒绝原因',
|
||
`seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卖家备注',
|
||
`shop_seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商家备注(移交供应商处理时保存)',
|
||
`handel_time` datetime DEFAULT NULL COMMENT '受理时间',
|
||
`supplier_time` datetime DEFAULT NULL COMMENT '移交供应商时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`receive_time` datetime DEFAULT NULL COMMENT '收货时间',
|
||
`close_time` datetime DEFAULT NULL COMMENT '关闭时间',
|
||
`decision_time` datetime DEFAULT NULL COMMENT '确定时间(确定退款时间)',
|
||
`refund_time` datetime DEFAULT NULL COMMENT '退款时间',
|
||
`supplier_id` bigint NOT NULL DEFAULT '0' COMMENT '供应商id',
|
||
`supplier_handle_status` tinyint DEFAULT '-1' COMMENT '供应商处理状态， -1.商家自行处理 0.待供应商处理 1.供应商已同意 2.供应商已拒绝',
|
||
`purchase_refund_amount` bigint DEFAULT '0' COMMENT '采购退款金额',
|
||
`pur_platform_refund_commission` bigint DEFAULT '0' COMMENT '采购的平台佣金退款金额',
|
||
`return_giveaway_ids` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款退回的赠品订单项ids',
|
||
`refund_actual_total` decimal(15,2) DEFAULT NULL COMMENT '当前退款的实付金额（实付金额减去不退回赠品的金额）',
|
||
`platform_intervention_status` tinyint NOT NULL DEFAULT '-1' COMMENT '平台介入状态 -1.没有介入 1.用户申请介入 2.平台同意介入 3.平台拒绝介入 5.平台同意退款成功',
|
||
`apply_intervention_reason` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户申请介入理由',
|
||
`apply_intervention_img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请介入的文件凭证(逗号隔开)',
|
||
`intervention_refund_type` tinyint DEFAULT NULL COMMENT '平台介入退款方式 1.商家承担 2.商家供应商承担（供应商发货订单） 3.不同意退款',
|
||
`after_intervention_refund_amount` bigint DEFAULT NULL COMMENT '平台介入前的退款金额',
|
||
`platform_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '平台留言',
|
||
`apply_intervention_time` datetime DEFAULT NULL COMMENT '申请介入时间',
|
||
`intervention_finish_time` datetime DEFAULT NULL COMMENT '介入完成时间',
|
||
PRIMARY KEY (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_item_id` (`order_item_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款记录信息'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_table FROM @create_order_refund_table;
|
||
EXECUTE create_order_refund_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_intervention_', i,
|
||
"
|
||
(
|
||
`refund_intervertion_id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`biz_id` bigint DEFAULT '0' COMMENT '商家/供应商/用户id',
|
||
`sys_type` tinyint DEFAULT NULL COMMENT '系统类型，0.普通用户 1.商家端 2.平台端 3.供应商端',
|
||
`voucher_desc` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '凭证说明',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
PRIMARY KEY (`refund_intervertion_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_biz_id` (`biz_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款介入记录'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_addr_', i,
|
||
"
|
||
(
|
||
`refund_addr_id` bigint NOT NULL AUTO_INCREMENT COMMENT '物流ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款号',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`delivery_company_id` bigint DEFAULT NULL COMMENT '物流公司ID',
|
||
`delivery_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流公司名称',
|
||
`delivery_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流单号',
|
||
`consignee_name` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人姓名',
|
||
`consignee_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人电话（顺丰快递需要）',
|
||
`consignee_telephone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人座机',
|
||
`consignee_post_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人邮政编码',
|
||
`consignee_addr` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人地址',
|
||
`sender_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发送人手机号码',
|
||
`sender_remarks` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '买家备注',
|
||
`imgs` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片凭证',
|
||
PRIMARY KEY (`refund_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_delivery_company_id` (`delivery_company_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户退货物流地址'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_settlement_', i,
|
||
"
|
||
(
|
||
`settlement_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '退款结算单据id',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`pay_id` bigint DEFAULT NULL COMMENT '订单支付单号',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`refund_id` bigint DEFAULT NULL COMMENT '退款单编号（本系统退款单号）',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`biz_refund_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信/支付宝退款单号（支付平台退款单号）',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式(1:微信支付 2支付宝支付)',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '退款状态(1:申请中 2：已完成 -1失败)',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`order_total_amount` bigint DEFAULT NULL COMMENT '订单总额',
|
||
`version` int DEFAULT '0' COMMENT '版本号',
|
||
PRIMARY KEY (`settlement_id`),
|
||
UNIQUE KEY `uni_refund_id` (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_pay_id` (`pay_id`),
|
||
KEY `idx_biz_refund_no` (`biz_refund_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='退款支付结算单据'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
# ============================== 采购订单 ==============================
|
||
SET i = 2;
|
||
WHILE i<64 DO
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_', i,
|
||
"(`purchase_order_id` bigint NOT NULL AUTO_INCREMENT COMMENT '采购id',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deliver_time` datetime DEFAULT NULL COMMENT '送达时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`supplier_id` bigint NOT NULL COMMENT '供应商id',
|
||
`total_amount` bigint NOT NULL COMMENT '总采购金额',
|
||
`total_stock` int NOT NULL COMMENT '总采购库存数量',
|
||
`actual_total_stock` int DEFAULT NULL COMMENT '实际总库存数量',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
|
||
`employee_id` bigint DEFAULT NULL COMMENT '操作员工id',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '配送类型 1:快递 2:自提 3：无需快递 4同城配送',
|
||
`purchase_order_addr_id` bigint DEFAULT NULL COMMENT '采购订单地址Id',
|
||
`voucher_img_urls` VARCHAR(1000) DEFAULT NULL COMMENT '付款凭证图片 多个图片逗号分隔',
|
||
`warehouse_id` bigint DEFAULT NULL COMMENT '仓库id',
|
||
`supplier_warehouse_id` bigint DEFAULT NULL COMMENT '供应商仓库id',
|
||
`stock_point_type` int DEFAULT NULL COMMENT '库存点类型(1:仓库, 2:门店)',
|
||
PRIMARY KEY (`purchase_order_id`),
|
||
KEY `idx_supplier_id` (`supplier_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_lang_', i,
|
||
"
|
||
(
|
||
`purchase_order_id` bigint NOT NULL,
|
||
`lang` tinyint NOT NULL,
|
||
`spu_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
|
||
PRIMARY KEY (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT 'id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`purchase_order_id` bigint DEFAULT NULL COMMENT '采购订单id',
|
||
`spu_id` bigint DEFAULT NULL COMMENT '商品id',
|
||
`sku_id` bigint DEFAULT NULL COMMENT 'sku id',
|
||
`spu_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品编码',
|
||
`party_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku编码',
|
||
`pic` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品图片',
|
||
`purchase_amount` bigint DEFAULT NULL COMMENT '采购金额',
|
||
`purchase_price` bigint DEFAULT NULL COMMENT '采购价',
|
||
`purchase_stock` int DEFAULT NULL COMMENT '采购库存数量',
|
||
`actual_stock` int DEFAULT NULL COMMENT '实际库存数量',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '订单项退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`be_delivered_num` int DEFAULT NULL COMMENT '0全部发货 其他数量为剩余待发货数量',
|
||
PRIMARY KEY (`purchase_item_id`),
|
||
KEY `idx_purchase_order_id` (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单项'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_lang_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT '订单项ID',
|
||
`lang` tinyint NOT NULL COMMENT '语言 1.中文 2.英文',
|
||
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
PRIMARY KEY (`purchase_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_addr_', i,
|
||
"
|
||
(
|
||
`purchase_order_addr_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`consignee` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收货人',
|
||
`province_id` bigint DEFAULT NULL COMMENT '省ID',
|
||
`province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省',
|
||
`city_id` bigint DEFAULT NULL COMMENT '城市ID',
|
||
`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
|
||
`area_id` bigint DEFAULT NULL COMMENT '区域ID',
|
||
`area` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
|
||
`addr` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
|
||
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机',
|
||
PRIMARY KEY (`purchase_order_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单配送地址'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
|
||
USE tmerclub_order_2;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
# ============================== 订单退款 ==============================
|
||
SET i = 32;
|
||
WHILE i<64 DO
|
||
SET @create_order_refund_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_', i,
|
||
"
|
||
(
|
||
`refund_id` bigint NOT NULL COMMENT '记录ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint NOT NULL COMMENT '店铺ID',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`order_id` bigint NOT NULL COMMENT '订单号',
|
||
`order_item_id` bigint NOT NULL DEFAULT '0' COMMENT '订单项ID(0:为全部订单项)',
|
||
`refund_count` int DEFAULT '0' COMMENT '退货数量(0:为全部订单项)',
|
||
`refund_score` bigint DEFAULT NULL COMMENT '退还积分',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`platform_refund_commission` bigint DEFAULT NULL COMMENT '平台佣金退款金额',
|
||
`platform_refund_amount` bigint DEFAULT NULL COMMENT '平台退款金额（退款时将这部分钱退回给平台，所以商家要扣除从平台这里获取的金额）',
|
||
`distribution_total_amount` bigint DEFAULT NULL COMMENT '退款单总分销金额',
|
||
`refund_type` tinyint NOT NULL DEFAULT '0' COMMENT '退款单类型（1:整单退款,2:单个物品退款）',
|
||
`apply_type` tinyint NOT NULL COMMENT '申请类型:1,仅退款,2退款退货',
|
||
`pay_sys_type` tinyint NOT NULL DEFAULT '0' COMMENT '支付系统类型 0默认1通联支付',
|
||
`is_received` tinyint DEFAULT NULL COMMENT '是否接收到商品(1:已收到,0:未收到)',
|
||
`close_type` tinyint DEFAULT NULL COMMENT '退款关闭原因(1.买家撤销退款 2.卖家拒绝退款 3.退款申请超时被系统关闭)',
|
||
`return_money_sts` tinyint NOT NULL DEFAULT '0' COMMENT '处理退款状态:(1.买家申请 2.卖家接受 3.买家发货 4.卖家收货 5.退款成功 -1.退款关闭)详情见ReturnMoneyStsType',
|
||
`buyer_reason` tinyint DEFAULT NULL COMMENT '申请原因(具体见BuyerReasonType)',
|
||
`buyer_desc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请说明',
|
||
`buyer_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系方式（退款时留下的手机号码）',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
`shop_img_urls` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ''商家/供应商文件凭证(逗号隔开)'',
|
||
`over_time` datetime DEFAULT NULL COMMENT '超时时间（超过该时间不处理，系统将自动处理）（保留字段）',
|
||
`reject_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '拒绝原因',
|
||
`seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卖家备注',
|
||
`shop_seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商家备注(移交供应商处理时保存)',
|
||
`handel_time` datetime DEFAULT NULL COMMENT '受理时间',
|
||
`supplier_time` datetime DEFAULT NULL COMMENT '移交供应商时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`receive_time` datetime DEFAULT NULL COMMENT '收货时间',
|
||
`close_time` datetime DEFAULT NULL COMMENT '关闭时间',
|
||
`decision_time` datetime DEFAULT NULL COMMENT '确定时间(确定退款时间)',
|
||
`refund_time` datetime DEFAULT NULL COMMENT '退款时间',
|
||
`supplier_id` bigint NOT NULL DEFAULT '0' COMMENT '供应商id',
|
||
`supplier_handle_status` tinyint DEFAULT '-1' COMMENT '供应商处理状态， -1.商家自行处理 0.待供应商处理 1.供应商已同意 2.供应商已拒绝',
|
||
`purchase_refund_amount` bigint DEFAULT '0' COMMENT '采购退款金额',
|
||
`pur_platform_refund_commission` bigint DEFAULT '0' COMMENT '采购的平台佣金退款金额',
|
||
`return_giveaway_ids` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款退回的赠品订单项ids',
|
||
`refund_actual_total` decimal(15,2) DEFAULT NULL COMMENT '当前退款的实付金额（实付金额减去不退回赠品的金额）',
|
||
`platform_intervention_status` tinyint NOT NULL DEFAULT '-1' COMMENT '平台介入状态 -1.没有介入 1.用户申请介入 2.平台同意介入 3.平台拒绝介入 5.平台同意退款成功',
|
||
`apply_intervention_reason` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户申请介入理由',
|
||
`apply_intervention_img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请介入的文件凭证(逗号隔开)',
|
||
`intervention_refund_type` tinyint DEFAULT NULL COMMENT '平台介入退款方式 1.商家承担 2.商家供应商承担（供应商发货订单） 3.不同意退款',
|
||
`after_intervention_refund_amount` bigint DEFAULT NULL COMMENT '平台介入前的退款金额',
|
||
`platform_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '平台留言',
|
||
`apply_intervention_time` datetime DEFAULT NULL COMMENT '申请介入时间',
|
||
`intervention_finish_time` datetime DEFAULT NULL COMMENT '介入完成时间',
|
||
PRIMARY KEY (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_item_id` (`order_item_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款记录信息'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_table FROM @create_order_refund_table;
|
||
EXECUTE create_order_refund_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_intervention_', i,
|
||
"
|
||
(
|
||
`refund_intervertion_id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`biz_id` bigint DEFAULT '0' COMMENT '商家/供应商/用户id',
|
||
`sys_type` tinyint DEFAULT NULL COMMENT '系统类型，0.普通用户 1.商家端 2.平台端 3.供应商端',
|
||
`voucher_desc` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '凭证说明',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
PRIMARY KEY (`refund_intervertion_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_biz_id` (`biz_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款介入记录'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_addr_', i,
|
||
"
|
||
(
|
||
`refund_addr_id` bigint NOT NULL AUTO_INCREMENT COMMENT '物流ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款号',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`delivery_company_id` bigint DEFAULT NULL COMMENT '物流公司ID',
|
||
`delivery_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流公司名称',
|
||
`delivery_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流单号',
|
||
`consignee_name` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人姓名',
|
||
`consignee_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人电话（顺丰快递需要）',
|
||
`consignee_telephone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人座机',
|
||
`consignee_post_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人邮政编码',
|
||
`consignee_addr` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人地址',
|
||
`sender_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发送人手机号码',
|
||
`sender_remarks` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '买家备注',
|
||
`imgs` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片凭证',
|
||
PRIMARY KEY (`refund_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_delivery_company_id` (`delivery_company_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户退货物流地址'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_settlement_', i,
|
||
"
|
||
(
|
||
`settlement_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '退款结算单据id',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`pay_id` bigint DEFAULT NULL COMMENT '订单支付单号',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`refund_id` bigint DEFAULT NULL COMMENT '退款单编号（本系统退款单号）',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`biz_refund_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信/支付宝退款单号（支付平台退款单号）',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式(1:微信支付 2支付宝支付)',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '退款状态(1:申请中 2：已完成 -1失败)',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`order_total_amount` bigint DEFAULT NULL COMMENT '订单总额',
|
||
`version` int DEFAULT '0' COMMENT '版本号',
|
||
PRIMARY KEY (`settlement_id`),
|
||
UNIQUE KEY `uni_refund_id` (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_pay_id` (`pay_id`),
|
||
KEY `idx_biz_refund_no` (`biz_refund_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='退款支付结算单据'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
# ============================== 采购订单 ==============================
|
||
SET i = 2;
|
||
WHILE i<64 DO
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_', i,
|
||
"(`purchase_order_id` bigint NOT NULL AUTO_INCREMENT COMMENT '采购id',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deliver_time` datetime DEFAULT NULL COMMENT '送达时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`supplier_id` bigint NOT NULL COMMENT '供应商id',
|
||
`total_amount` bigint NOT NULL COMMENT '总采购金额',
|
||
`total_stock` int NOT NULL COMMENT '总采购库存数量',
|
||
`actual_total_stock` int DEFAULT NULL COMMENT '实际总库存数量',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
|
||
`employee_id` bigint DEFAULT NULL COMMENT '操作员工id',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '配送类型 1:快递 2:自提 3：无需快递 4同城配送',
|
||
`purchase_order_addr_id` bigint DEFAULT NULL COMMENT '采购订单地址Id',
|
||
`voucher_img_urls` VARCHAR(1000) DEFAULT NULL COMMENT '付款凭证图片 多个图片逗号分隔',
|
||
`warehouse_id` bigint DEFAULT NULL COMMENT '仓库id',
|
||
`supplier_warehouse_id` bigint DEFAULT NULL COMMENT '供应商仓库id',
|
||
`stock_point_type` int DEFAULT NULL COMMENT '库存点类型(1:仓库, 2:门店)',
|
||
PRIMARY KEY (`purchase_order_id`),
|
||
KEY `idx_supplier_id` (`supplier_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_lang_', i,
|
||
"
|
||
(
|
||
`purchase_order_id` bigint NOT NULL,
|
||
`lang` tinyint NOT NULL,
|
||
`spu_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
|
||
PRIMARY KEY (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT 'id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`purchase_order_id` bigint DEFAULT NULL COMMENT '采购订单id',
|
||
`spu_id` bigint DEFAULT NULL COMMENT '商品id',
|
||
`sku_id` bigint DEFAULT NULL COMMENT 'sku id',
|
||
`spu_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品编码',
|
||
`party_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku编码',
|
||
`pic` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品图片',
|
||
`purchase_amount` bigint DEFAULT NULL COMMENT '采购金额',
|
||
`purchase_price` bigint DEFAULT NULL COMMENT '采购价',
|
||
`purchase_stock` int DEFAULT NULL COMMENT '采购库存数量',
|
||
`actual_stock` int DEFAULT NULL COMMENT '实际库存数量',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '订单项退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`be_delivered_num` int DEFAULT NULL COMMENT '0全部发货 其他数量为剩余待发货数量',
|
||
PRIMARY KEY (`purchase_item_id`),
|
||
KEY `idx_purchase_order_id` (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单项'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_lang_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT '订单项ID',
|
||
`lang` tinyint NOT NULL COMMENT '语言 1.中文 2.英文',
|
||
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
PRIMARY KEY (`purchase_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_addr_', i,
|
||
"
|
||
(
|
||
`purchase_order_addr_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`consignee` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收货人',
|
||
`province_id` bigint DEFAULT NULL COMMENT '省ID',
|
||
`province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省',
|
||
`city_id` bigint DEFAULT NULL COMMENT '城市ID',
|
||
`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
|
||
`area_id` bigint DEFAULT NULL COMMENT '区域ID',
|
||
`area` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
|
||
`addr` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
|
||
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机',
|
||
PRIMARY KEY (`purchase_order_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单配送地址'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
|
||
USE tmerclub_order_3;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
# ============================== 订单退款 ==============================
|
||
SET i = 32;
|
||
WHILE i<64 DO
|
||
SET @create_order_refund_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_', i,
|
||
"
|
||
(
|
||
`refund_id` bigint NOT NULL COMMENT '记录ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint NOT NULL COMMENT '店铺ID',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`order_id` bigint NOT NULL COMMENT '订单号',
|
||
`order_item_id` bigint NOT NULL DEFAULT '0' COMMENT '订单项ID(0:为全部订单项)',
|
||
`refund_count` int DEFAULT '0' COMMENT '退货数量(0:为全部订单项)',
|
||
`refund_score` bigint DEFAULT NULL COMMENT '退还积分',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`platform_refund_commission` bigint DEFAULT NULL COMMENT '平台佣金退款金额',
|
||
`platform_refund_amount` bigint DEFAULT NULL COMMENT '平台退款金额（退款时将这部分钱退回给平台，所以商家要扣除从平台这里获取的金额）',
|
||
`distribution_total_amount` bigint DEFAULT NULL COMMENT '退款单总分销金额',
|
||
`refund_type` tinyint NOT NULL DEFAULT '0' COMMENT '退款单类型（1:整单退款,2:单个物品退款）',
|
||
`apply_type` tinyint NOT NULL COMMENT '申请类型:1,仅退款,2退款退货',
|
||
`pay_sys_type` tinyint NOT NULL DEFAULT '0' COMMENT '支付系统类型 0默认1通联支付',
|
||
`is_received` tinyint DEFAULT NULL COMMENT '是否接收到商品(1:已收到,0:未收到)',
|
||
`close_type` tinyint DEFAULT NULL COMMENT '退款关闭原因(1.买家撤销退款 2.卖家拒绝退款 3.退款申请超时被系统关闭)',
|
||
`return_money_sts` tinyint NOT NULL DEFAULT '0' COMMENT '处理退款状态:(1.买家申请 2.卖家接受 3.买家发货 4.卖家收货 5.退款成功 -1.退款关闭)详情见ReturnMoneyStsType',
|
||
`buyer_reason` tinyint DEFAULT NULL COMMENT '申请原因(具体见BuyerReasonType)',
|
||
`buyer_desc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请说明',
|
||
`buyer_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系方式（退款时留下的手机号码）',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
`shop_img_urls` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ''商家/供应商文件凭证(逗号隔开)'',
|
||
`over_time` datetime DEFAULT NULL COMMENT '超时时间（超过该时间不处理，系统将自动处理）（保留字段）',
|
||
`reject_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '拒绝原因',
|
||
`seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卖家备注',
|
||
`shop_seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商家备注(移交供应商处理时保存)',
|
||
`handel_time` datetime DEFAULT NULL COMMENT '受理时间',
|
||
`supplier_time` datetime DEFAULT NULL COMMENT '移交供应商时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`receive_time` datetime DEFAULT NULL COMMENT '收货时间',
|
||
`close_time` datetime DEFAULT NULL COMMENT '关闭时间',
|
||
`decision_time` datetime DEFAULT NULL COMMENT '确定时间(确定退款时间)',
|
||
`refund_time` datetime DEFAULT NULL COMMENT '退款时间',
|
||
`supplier_id` bigint NOT NULL DEFAULT '0' COMMENT '供应商id',
|
||
`supplier_handle_status` tinyint DEFAULT '-1' COMMENT '供应商处理状态， -1.商家自行处理 0.待供应商处理 1.供应商已同意 2.供应商已拒绝',
|
||
`purchase_refund_amount` bigint DEFAULT '0' COMMENT '采购退款金额',
|
||
`pur_platform_refund_commission` bigint DEFAULT '0' COMMENT '采购的平台佣金退款金额',
|
||
`return_giveaway_ids` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款退回的赠品订单项ids',
|
||
`refund_actual_total` decimal(15,2) DEFAULT NULL COMMENT '当前退款的实付金额（实付金额减去不退回赠品的金额）',
|
||
`platform_intervention_status` tinyint NOT NULL DEFAULT '-1' COMMENT '平台介入状态 -1.没有介入 1.用户申请介入 2.平台同意介入 3.平台拒绝介入 5.平台同意退款成功',
|
||
`apply_intervention_reason` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户申请介入理由',
|
||
`apply_intervention_img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请介入的文件凭证(逗号隔开)',
|
||
`intervention_refund_type` tinyint DEFAULT NULL COMMENT '平台介入退款方式 1.商家承担 2.商家供应商承担（供应商发货订单） 3.不同意退款',
|
||
`after_intervention_refund_amount` bigint DEFAULT NULL COMMENT '平台介入前的退款金额',
|
||
`platform_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '平台留言',
|
||
`apply_intervention_time` datetime DEFAULT NULL COMMENT '申请介入时间',
|
||
`intervention_finish_time` datetime DEFAULT NULL COMMENT '介入完成时间',
|
||
PRIMARY KEY (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_item_id` (`order_item_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款记录信息'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_table FROM @create_order_refund_table;
|
||
EXECUTE create_order_refund_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_intervention_', i,
|
||
"
|
||
(
|
||
`refund_intervertion_id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`biz_id` bigint DEFAULT '0' COMMENT '商家/供应商/用户id',
|
||
`sys_type` tinyint DEFAULT NULL COMMENT '系统类型，0.普通用户 1.商家端 2.平台端 3.供应商端',
|
||
`voucher_desc` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '凭证说明',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
PRIMARY KEY (`refund_intervertion_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_biz_id` (`biz_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款介入记录'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_addr_', i,
|
||
"
|
||
(
|
||
`refund_addr_id` bigint NOT NULL AUTO_INCREMENT COMMENT '物流ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款号',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`delivery_company_id` bigint DEFAULT NULL COMMENT '物流公司ID',
|
||
`delivery_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流公司名称',
|
||
`delivery_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流单号',
|
||
`consignee_name` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人姓名',
|
||
`consignee_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人电话（顺丰快递需要）',
|
||
`consignee_telephone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人座机',
|
||
`consignee_post_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人邮政编码',
|
||
`consignee_addr` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人地址',
|
||
`sender_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发送人手机号码',
|
||
`sender_remarks` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '买家备注',
|
||
`imgs` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片凭证',
|
||
PRIMARY KEY (`refund_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_delivery_company_id` (`delivery_company_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户退货物流地址'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_settlement_', i,
|
||
"
|
||
(
|
||
`settlement_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '退款结算单据id',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`pay_id` bigint DEFAULT NULL COMMENT '订单支付单号',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`refund_id` bigint DEFAULT NULL COMMENT '退款单编号（本系统退款单号）',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`biz_refund_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信/支付宝退款单号（支付平台退款单号）',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式(1:微信支付 2支付宝支付)',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '退款状态(1:申请中 2：已完成 -1失败)',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`order_total_amount` bigint DEFAULT NULL COMMENT '订单总额',
|
||
`version` int DEFAULT '0' COMMENT '版本号',
|
||
PRIMARY KEY (`settlement_id`),
|
||
UNIQUE KEY `uni_refund_id` (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_pay_id` (`pay_id`),
|
||
KEY `idx_biz_refund_no` (`biz_refund_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='退款支付结算单据'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
# ============================== 采购订单 ==============================
|
||
SET i = 2;
|
||
WHILE i<64 DO
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_', i,
|
||
"(`purchase_order_id` bigint NOT NULL AUTO_INCREMENT COMMENT '采购id',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deliver_time` datetime DEFAULT NULL COMMENT '送达时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`supplier_id` bigint NOT NULL COMMENT '供应商id',
|
||
`total_amount` bigint NOT NULL COMMENT '总采购金额',
|
||
`total_stock` int NOT NULL COMMENT '总采购库存数量',
|
||
`actual_total_stock` int DEFAULT NULL COMMENT '实际总库存数量',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
|
||
`employee_id` bigint DEFAULT NULL COMMENT '操作员工id',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '配送类型 1:快递 2:自提 3：无需快递 4同城配送',
|
||
`purchase_order_addr_id` bigint DEFAULT NULL COMMENT '采购订单地址Id',
|
||
`voucher_img_urls` VARCHAR(1000) DEFAULT NULL COMMENT '付款凭证图片 多个图片逗号分隔',
|
||
`warehouse_id` bigint DEFAULT NULL COMMENT '仓库id',
|
||
`supplier_warehouse_id` bigint DEFAULT NULL COMMENT '供应商仓库id',
|
||
`stock_point_type` int DEFAULT NULL COMMENT '库存点类型(1:仓库, 2:门店)',
|
||
PRIMARY KEY (`purchase_order_id`),
|
||
KEY `idx_supplier_id` (`supplier_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_lang_', i,
|
||
"
|
||
(
|
||
`purchase_order_id` bigint NOT NULL,
|
||
`lang` tinyint NOT NULL,
|
||
`spu_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
|
||
PRIMARY KEY (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT 'id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`purchase_order_id` bigint DEFAULT NULL COMMENT '采购订单id',
|
||
`spu_id` bigint DEFAULT NULL COMMENT '商品id',
|
||
`sku_id` bigint DEFAULT NULL COMMENT 'sku id',
|
||
`spu_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品编码',
|
||
`party_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku编码',
|
||
`pic` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品图片',
|
||
`purchase_amount` bigint DEFAULT NULL COMMENT '采购金额',
|
||
`purchase_price` bigint DEFAULT NULL COMMENT '采购价',
|
||
`purchase_stock` int DEFAULT NULL COMMENT '采购库存数量',
|
||
`actual_stock` int DEFAULT NULL COMMENT '实际库存数量',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '订单项退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`be_delivered_num` int DEFAULT NULL COMMENT '0全部发货 其他数量为剩余待发货数量',
|
||
PRIMARY KEY (`purchase_item_id`),
|
||
KEY `idx_purchase_order_id` (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单项'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_lang_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT '订单项ID',
|
||
`lang` tinyint NOT NULL COMMENT '语言 1.中文 2.英文',
|
||
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
PRIMARY KEY (`purchase_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_addr_', i,
|
||
"
|
||
(
|
||
`purchase_order_addr_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`consignee` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收货人',
|
||
`province_id` bigint DEFAULT NULL COMMENT '省ID',
|
||
`province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省',
|
||
`city_id` bigint DEFAULT NULL COMMENT '城市ID',
|
||
`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
|
||
`area_id` bigint DEFAULT NULL COMMENT '区域ID',
|
||
`area` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
|
||
`addr` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
|
||
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机',
|
||
PRIMARY KEY (`purchase_order_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单配送地址'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_4;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
# ============================== 订单退款 ==============================
|
||
SET i = 32;
|
||
WHILE i<64 DO
|
||
SET @create_order_refund_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_', i,
|
||
"
|
||
(
|
||
`refund_id` bigint NOT NULL COMMENT '记录ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint NOT NULL COMMENT '店铺ID',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`order_id` bigint NOT NULL COMMENT '订单号',
|
||
`order_item_id` bigint NOT NULL DEFAULT '0' COMMENT '订单项ID(0:为全部订单项)',
|
||
`refund_count` int DEFAULT '0' COMMENT '退货数量(0:为全部订单项)',
|
||
`refund_score` bigint DEFAULT NULL COMMENT '退还积分',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`platform_refund_commission` bigint DEFAULT NULL COMMENT '平台佣金退款金额',
|
||
`platform_refund_amount` bigint DEFAULT NULL COMMENT '平台退款金额（退款时将这部分钱退回给平台，所以商家要扣除从平台这里获取的金额）',
|
||
`distribution_total_amount` bigint DEFAULT NULL COMMENT '退款单总分销金额',
|
||
`refund_type` tinyint NOT NULL DEFAULT '0' COMMENT '退款单类型（1:整单退款,2:单个物品退款）',
|
||
`apply_type` tinyint NOT NULL COMMENT '申请类型:1,仅退款,2退款退货',
|
||
`pay_sys_type` tinyint NOT NULL DEFAULT '0' COMMENT '支付系统类型 0默认1通联支付',
|
||
`is_received` tinyint DEFAULT NULL COMMENT '是否接收到商品(1:已收到,0:未收到)',
|
||
`close_type` tinyint DEFAULT NULL COMMENT '退款关闭原因(1.买家撤销退款 2.卖家拒绝退款 3.退款申请超时被系统关闭)',
|
||
`return_money_sts` tinyint NOT NULL DEFAULT '0' COMMENT '处理退款状态:(1.买家申请 2.卖家接受 3.买家发货 4.卖家收货 5.退款成功 -1.退款关闭)详情见ReturnMoneyStsType',
|
||
`buyer_reason` tinyint DEFAULT NULL COMMENT '申请原因(具体见BuyerReasonType)',
|
||
`buyer_desc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请说明',
|
||
`buyer_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系方式（退款时留下的手机号码）',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
`shop_img_urls` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ''商家/供应商文件凭证(逗号隔开)'',
|
||
`over_time` datetime DEFAULT NULL COMMENT '超时时间（超过该时间不处理，系统将自动处理）（保留字段）',
|
||
`reject_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '拒绝原因',
|
||
`seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卖家备注',
|
||
`shop_seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商家备注(移交供应商处理时保存)',
|
||
`handel_time` datetime DEFAULT NULL COMMENT '受理时间',
|
||
`supplier_time` datetime DEFAULT NULL COMMENT '移交供应商时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`receive_time` datetime DEFAULT NULL COMMENT '收货时间',
|
||
`close_time` datetime DEFAULT NULL COMMENT '关闭时间',
|
||
`decision_time` datetime DEFAULT NULL COMMENT '确定时间(确定退款时间)',
|
||
`refund_time` datetime DEFAULT NULL COMMENT '退款时间',
|
||
`supplier_id` bigint NOT NULL DEFAULT '0' COMMENT '供应商id',
|
||
`supplier_handle_status` tinyint DEFAULT '-1' COMMENT '供应商处理状态， -1.商家自行处理 0.待供应商处理 1.供应商已同意 2.供应商已拒绝',
|
||
`purchase_refund_amount` bigint DEFAULT '0' COMMENT '采购退款金额',
|
||
`pur_platform_refund_commission` bigint DEFAULT '0' COMMENT '采购的平台佣金退款金额',
|
||
`return_giveaway_ids` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款退回的赠品订单项ids',
|
||
`refund_actual_total` decimal(15,2) DEFAULT NULL COMMENT '当前退款的实付金额（实付金额减去不退回赠品的金额）',
|
||
`platform_intervention_status` tinyint NOT NULL DEFAULT '-1' COMMENT '平台介入状态 -1.没有介入 1.用户申请介入 2.平台同意介入 3.平台拒绝介入 5.平台同意退款成功',
|
||
`apply_intervention_reason` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户申请介入理由',
|
||
`apply_intervention_img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请介入的文件凭证(逗号隔开)',
|
||
`intervention_refund_type` tinyint DEFAULT NULL COMMENT '平台介入退款方式 1.商家承担 2.商家供应商承担（供应商发货订单） 3.不同意退款',
|
||
`after_intervention_refund_amount` bigint DEFAULT NULL COMMENT '平台介入前的退款金额',
|
||
`platform_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '平台留言',
|
||
`apply_intervention_time` datetime DEFAULT NULL COMMENT '申请介入时间',
|
||
`intervention_finish_time` datetime DEFAULT NULL COMMENT '介入完成时间',
|
||
PRIMARY KEY (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_item_id` (`order_item_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款记录信息'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_table FROM @create_order_refund_table;
|
||
EXECUTE create_order_refund_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_intervention_', i,
|
||
"
|
||
(
|
||
`refund_intervertion_id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`biz_id` bigint DEFAULT '0' COMMENT '商家/供应商/用户id',
|
||
`sys_type` tinyint DEFAULT NULL COMMENT '系统类型，0.普通用户 1.商家端 2.平台端 3.供应商端',
|
||
`voucher_desc` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '凭证说明',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
PRIMARY KEY (`refund_intervertion_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_biz_id` (`biz_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款介入记录'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_addr_', i,
|
||
"
|
||
(
|
||
`refund_addr_id` bigint NOT NULL AUTO_INCREMENT COMMENT '物流ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款号',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`delivery_company_id` bigint DEFAULT NULL COMMENT '物流公司ID',
|
||
`delivery_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流公司名称',
|
||
`delivery_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流单号',
|
||
`consignee_name` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人姓名',
|
||
`consignee_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人电话（顺丰快递需要）',
|
||
`consignee_telephone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人座机',
|
||
`consignee_post_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人邮政编码',
|
||
`consignee_addr` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人地址',
|
||
`sender_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发送人手机号码',
|
||
`sender_remarks` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '买家备注',
|
||
`imgs` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片凭证',
|
||
PRIMARY KEY (`refund_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_delivery_company_id` (`delivery_company_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户退货物流地址'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_settlement_', i,
|
||
"
|
||
(
|
||
`settlement_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '退款结算单据id',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`pay_id` bigint DEFAULT NULL COMMENT '订单支付单号',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`refund_id` bigint DEFAULT NULL COMMENT '退款单编号（本系统退款单号）',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`biz_refund_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信/支付宝退款单号（支付平台退款单号）',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式(1:微信支付 2支付宝支付)',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '退款状态(1:申请中 2：已完成 -1失败)',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`order_total_amount` bigint DEFAULT NULL COMMENT '订单总额',
|
||
`version` int DEFAULT '0' COMMENT '版本号',
|
||
PRIMARY KEY (`settlement_id`),
|
||
UNIQUE KEY `uni_refund_id` (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_pay_id` (`pay_id`),
|
||
KEY `idx_biz_refund_no` (`biz_refund_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='退款支付结算单据'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
# ============================== 采购订单 ==============================
|
||
SET i = 2;
|
||
WHILE i<64 DO
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_', i,
|
||
"(`purchase_order_id` bigint NOT NULL AUTO_INCREMENT COMMENT '采购id',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deliver_time` datetime DEFAULT NULL COMMENT '送达时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`supplier_id` bigint NOT NULL COMMENT '供应商id',
|
||
`total_amount` bigint NOT NULL COMMENT '总采购金额',
|
||
`total_stock` int NOT NULL COMMENT '总采购库存数量',
|
||
`actual_total_stock` int DEFAULT NULL COMMENT '实际总库存数量',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
|
||
`employee_id` bigint DEFAULT NULL COMMENT '操作员工id',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '配送类型 1:快递 2:自提 3：无需快递 4同城配送',
|
||
`purchase_order_addr_id` bigint DEFAULT NULL COMMENT '采购订单地址Id',
|
||
`voucher_img_urls` VARCHAR(1000) DEFAULT NULL COMMENT '付款凭证图片 多个图片逗号分隔',
|
||
`warehouse_id` bigint DEFAULT NULL COMMENT '仓库id',
|
||
`supplier_warehouse_id` bigint DEFAULT NULL COMMENT '供应商仓库id',
|
||
`stock_point_type` int DEFAULT NULL COMMENT '库存点类型(1:仓库, 2:门店)',
|
||
PRIMARY KEY (`purchase_order_id`),
|
||
KEY `idx_supplier_id` (`supplier_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_lang_', i,
|
||
"
|
||
(
|
||
`purchase_order_id` bigint NOT NULL,
|
||
`lang` tinyint NOT NULL,
|
||
`spu_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
|
||
PRIMARY KEY (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT 'id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`purchase_order_id` bigint DEFAULT NULL COMMENT '采购订单id',
|
||
`spu_id` bigint DEFAULT NULL COMMENT '商品id',
|
||
`sku_id` bigint DEFAULT NULL COMMENT 'sku id',
|
||
`spu_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品编码',
|
||
`party_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku编码',
|
||
`pic` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品图片',
|
||
`purchase_amount` bigint DEFAULT NULL COMMENT '采购金额',
|
||
`purchase_price` bigint DEFAULT NULL COMMENT '采购价',
|
||
`purchase_stock` int DEFAULT NULL COMMENT '采购库存数量',
|
||
`actual_stock` int DEFAULT NULL COMMENT '实际库存数量',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '订单项退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`be_delivered_num` int DEFAULT NULL COMMENT '0全部发货 其他数量为剩余待发货数量',
|
||
PRIMARY KEY (`purchase_item_id`),
|
||
KEY `idx_purchase_order_id` (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单项'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_lang_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT '订单项ID',
|
||
`lang` tinyint NOT NULL COMMENT '语言 1.中文 2.英文',
|
||
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
PRIMARY KEY (`purchase_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_addr_', i,
|
||
"
|
||
(
|
||
`purchase_order_addr_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`consignee` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收货人',
|
||
`province_id` bigint DEFAULT NULL COMMENT '省ID',
|
||
`province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省',
|
||
`city_id` bigint DEFAULT NULL COMMENT '城市ID',
|
||
`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
|
||
`area_id` bigint DEFAULT NULL COMMENT '区域ID',
|
||
`area` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
|
||
`addr` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
|
||
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机',
|
||
PRIMARY KEY (`purchase_order_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单配送地址'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
|
||
USE tmerclub_order_5;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
# ============================== 订单退款 ==============================
|
||
SET i = 32;
|
||
WHILE i<64 DO
|
||
SET @create_order_refund_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_', i,
|
||
"
|
||
(
|
||
`refund_id` bigint NOT NULL COMMENT '记录ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint NOT NULL COMMENT '店铺ID',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`order_id` bigint NOT NULL COMMENT '订单号',
|
||
`order_item_id` bigint NOT NULL DEFAULT '0' COMMENT '订单项ID(0:为全部订单项)',
|
||
`refund_count` int DEFAULT '0' COMMENT '退货数量(0:为全部订单项)',
|
||
`refund_score` bigint DEFAULT NULL COMMENT '退还积分',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`platform_refund_commission` bigint DEFAULT NULL COMMENT '平台佣金退款金额',
|
||
`platform_refund_amount` bigint DEFAULT NULL COMMENT '平台退款金额（退款时将这部分钱退回给平台，所以商家要扣除从平台这里获取的金额）',
|
||
`distribution_total_amount` bigint DEFAULT NULL COMMENT '退款单总分销金额',
|
||
`refund_type` tinyint NOT NULL DEFAULT '0' COMMENT '退款单类型（1:整单退款,2:单个物品退款）',
|
||
`apply_type` tinyint NOT NULL COMMENT '申请类型:1,仅退款,2退款退货',
|
||
`pay_sys_type` tinyint NOT NULL DEFAULT '0' COMMENT '支付系统类型 0默认1通联支付',
|
||
`is_received` tinyint DEFAULT NULL COMMENT '是否接收到商品(1:已收到,0:未收到)',
|
||
`close_type` tinyint DEFAULT NULL COMMENT '退款关闭原因(1.买家撤销退款 2.卖家拒绝退款 3.退款申请超时被系统关闭)',
|
||
`return_money_sts` tinyint NOT NULL DEFAULT '0' COMMENT '处理退款状态:(1.买家申请 2.卖家接受 3.买家发货 4.卖家收货 5.退款成功 -1.退款关闭)详情见ReturnMoneyStsType',
|
||
`buyer_reason` tinyint DEFAULT NULL COMMENT '申请原因(具体见BuyerReasonType)',
|
||
`buyer_desc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请说明',
|
||
`buyer_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系方式（退款时留下的手机号码）',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
`shop_img_urls` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ''商家/供应商文件凭证(逗号隔开)'',
|
||
`over_time` datetime DEFAULT NULL COMMENT '超时时间（超过该时间不处理，系统将自动处理）（保留字段）',
|
||
`reject_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '拒绝原因',
|
||
`seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卖家备注',
|
||
`shop_seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商家备注(移交供应商处理时保存)',
|
||
`handel_time` datetime DEFAULT NULL COMMENT '受理时间',
|
||
`supplier_time` datetime DEFAULT NULL COMMENT '移交供应商时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`receive_time` datetime DEFAULT NULL COMMENT '收货时间',
|
||
`close_time` datetime DEFAULT NULL COMMENT '关闭时间',
|
||
`decision_time` datetime DEFAULT NULL COMMENT '确定时间(确定退款时间)',
|
||
`refund_time` datetime DEFAULT NULL COMMENT '退款时间',
|
||
`supplier_id` bigint NOT NULL DEFAULT '0' COMMENT '供应商id',
|
||
`supplier_handle_status` tinyint DEFAULT '-1' COMMENT '供应商处理状态， -1.商家自行处理 0.待供应商处理 1.供应商已同意 2.供应商已拒绝',
|
||
`purchase_refund_amount` bigint DEFAULT '0' COMMENT '采购退款金额',
|
||
`pur_platform_refund_commission` bigint DEFAULT '0' COMMENT '采购的平台佣金退款金额',
|
||
`return_giveaway_ids` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款退回的赠品订单项ids',
|
||
`refund_actual_total` decimal(15,2) DEFAULT NULL COMMENT '当前退款的实付金额（实付金额减去不退回赠品的金额）',
|
||
`platform_intervention_status` tinyint NOT NULL DEFAULT '-1' COMMENT '平台介入状态 -1.没有介入 1.用户申请介入 2.平台同意介入 3.平台拒绝介入 5.平台同意退款成功',
|
||
`apply_intervention_reason` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户申请介入理由',
|
||
`apply_intervention_img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请介入的文件凭证(逗号隔开)',
|
||
`intervention_refund_type` tinyint DEFAULT NULL COMMENT '平台介入退款方式 1.商家承担 2.商家供应商承担（供应商发货订单） 3.不同意退款',
|
||
`after_intervention_refund_amount` bigint DEFAULT NULL COMMENT '平台介入前的退款金额',
|
||
`platform_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '平台留言',
|
||
`apply_intervention_time` datetime DEFAULT NULL COMMENT '申请介入时间',
|
||
`intervention_finish_time` datetime DEFAULT NULL COMMENT '介入完成时间',
|
||
PRIMARY KEY (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_item_id` (`order_item_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款记录信息'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_table FROM @create_order_refund_table;
|
||
EXECUTE create_order_refund_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_intervention_', i,
|
||
"
|
||
(
|
||
`refund_intervertion_id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`biz_id` bigint DEFAULT '0' COMMENT '商家/供应商/用户id',
|
||
`sys_type` tinyint DEFAULT NULL COMMENT '系统类型，0.普通用户 1.商家端 2.平台端 3.供应商端',
|
||
`voucher_desc` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '凭证说明',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
PRIMARY KEY (`refund_intervertion_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_biz_id` (`biz_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款介入记录'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_addr_', i,
|
||
"
|
||
(
|
||
`refund_addr_id` bigint NOT NULL AUTO_INCREMENT COMMENT '物流ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款号',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`delivery_company_id` bigint DEFAULT NULL COMMENT '物流公司ID',
|
||
`delivery_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流公司名称',
|
||
`delivery_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流单号',
|
||
`consignee_name` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人姓名',
|
||
`consignee_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人电话（顺丰快递需要）',
|
||
`consignee_telephone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人座机',
|
||
`consignee_post_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人邮政编码',
|
||
`consignee_addr` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人地址',
|
||
`sender_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发送人手机号码',
|
||
`sender_remarks` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '买家备注',
|
||
`imgs` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片凭证',
|
||
PRIMARY KEY (`refund_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_delivery_company_id` (`delivery_company_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户退货物流地址'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_settlement_', i,
|
||
"
|
||
(
|
||
`settlement_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '退款结算单据id',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`pay_id` bigint DEFAULT NULL COMMENT '订单支付单号',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`refund_id` bigint DEFAULT NULL COMMENT '退款单编号（本系统退款单号）',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`biz_refund_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信/支付宝退款单号（支付平台退款单号）',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式(1:微信支付 2支付宝支付)',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '退款状态(1:申请中 2：已完成 -1失败)',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`order_total_amount` bigint DEFAULT NULL COMMENT '订单总额',
|
||
`version` int DEFAULT '0' COMMENT '版本号',
|
||
PRIMARY KEY (`settlement_id`),
|
||
UNIQUE KEY `uni_refund_id` (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_pay_id` (`pay_id`),
|
||
KEY `idx_biz_refund_no` (`biz_refund_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='退款支付结算单据'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
# ============================== 采购订单 ==============================
|
||
SET i = 2;
|
||
WHILE i<64 DO
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_', i,
|
||
"(`purchase_order_id` bigint NOT NULL AUTO_INCREMENT COMMENT '采购id',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deliver_time` datetime DEFAULT NULL COMMENT '送达时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`supplier_id` bigint NOT NULL COMMENT '供应商id',
|
||
`total_amount` bigint NOT NULL COMMENT '总采购金额',
|
||
`total_stock` int NOT NULL COMMENT '总采购库存数量',
|
||
`actual_total_stock` int DEFAULT NULL COMMENT '实际总库存数量',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
|
||
`employee_id` bigint DEFAULT NULL COMMENT '操作员工id',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '配送类型 1:快递 2:自提 3：无需快递 4同城配送',
|
||
`purchase_order_addr_id` bigint DEFAULT NULL COMMENT '采购订单地址Id',
|
||
`voucher_img_urls` VARCHAR(1000) DEFAULT NULL COMMENT '付款凭证图片 多个图片逗号分隔',
|
||
`warehouse_id` bigint DEFAULT NULL COMMENT '仓库id',
|
||
`supplier_warehouse_id` bigint DEFAULT NULL COMMENT '供应商仓库id',
|
||
`stock_point_type` int DEFAULT NULL COMMENT '库存点类型(1:仓库, 2:门店)',
|
||
PRIMARY KEY (`purchase_order_id`),
|
||
KEY `idx_supplier_id` (`supplier_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_lang_', i,
|
||
"
|
||
(
|
||
`purchase_order_id` bigint NOT NULL,
|
||
`lang` tinyint NOT NULL,
|
||
`spu_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
|
||
PRIMARY KEY (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT 'id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`purchase_order_id` bigint DEFAULT NULL COMMENT '采购订单id',
|
||
`spu_id` bigint DEFAULT NULL COMMENT '商品id',
|
||
`sku_id` bigint DEFAULT NULL COMMENT 'sku id',
|
||
`spu_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品编码',
|
||
`party_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku编码',
|
||
`pic` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品图片',
|
||
`purchase_amount` bigint DEFAULT NULL COMMENT '采购金额',
|
||
`purchase_price` bigint DEFAULT NULL COMMENT '采购价',
|
||
`purchase_stock` int DEFAULT NULL COMMENT '采购库存数量',
|
||
`actual_stock` int DEFAULT NULL COMMENT '实际库存数量',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '订单项退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`be_delivered_num` int DEFAULT NULL COMMENT '0全部发货 其他数量为剩余待发货数量',
|
||
PRIMARY KEY (`purchase_item_id`),
|
||
KEY `idx_purchase_order_id` (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单项'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_lang_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT '订单项ID',
|
||
`lang` tinyint NOT NULL COMMENT '语言 1.中文 2.英文',
|
||
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
PRIMARY KEY (`purchase_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_addr_', i,
|
||
"
|
||
(
|
||
`purchase_order_addr_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`consignee` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收货人',
|
||
`province_id` bigint DEFAULT NULL COMMENT '省ID',
|
||
`province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省',
|
||
`city_id` bigint DEFAULT NULL COMMENT '城市ID',
|
||
`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
|
||
`area_id` bigint DEFAULT NULL COMMENT '区域ID',
|
||
`area` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
|
||
`addr` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
|
||
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机',
|
||
PRIMARY KEY (`purchase_order_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单配送地址'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_6;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
# ============================== 订单退款 ==============================
|
||
SET i = 32;
|
||
WHILE i<64 DO
|
||
SET @create_order_refund_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_', i,
|
||
"
|
||
(
|
||
`refund_id` bigint NOT NULL COMMENT '记录ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint NOT NULL COMMENT '店铺ID',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`order_id` bigint NOT NULL COMMENT '订单号',
|
||
`order_item_id` bigint NOT NULL DEFAULT '0' COMMENT '订单项ID(0:为全部订单项)',
|
||
`refund_count` int DEFAULT '0' COMMENT '退货数量(0:为全部订单项)',
|
||
`refund_score` bigint DEFAULT NULL COMMENT '退还积分',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`platform_refund_commission` bigint DEFAULT NULL COMMENT '平台佣金退款金额',
|
||
`platform_refund_amount` bigint DEFAULT NULL COMMENT '平台退款金额（退款时将这部分钱退回给平台，所以商家要扣除从平台这里获取的金额）',
|
||
`distribution_total_amount` bigint DEFAULT NULL COMMENT '退款单总分销金额',
|
||
`refund_type` tinyint NOT NULL DEFAULT '0' COMMENT '退款单类型（1:整单退款,2:单个物品退款）',
|
||
`apply_type` tinyint NOT NULL COMMENT '申请类型:1,仅退款,2退款退货',
|
||
`pay_sys_type` tinyint NOT NULL DEFAULT '0' COMMENT '支付系统类型 0默认1通联支付',
|
||
`is_received` tinyint DEFAULT NULL COMMENT '是否接收到商品(1:已收到,0:未收到)',
|
||
`close_type` tinyint DEFAULT NULL COMMENT '退款关闭原因(1.买家撤销退款 2.卖家拒绝退款 3.退款申请超时被系统关闭)',
|
||
`return_money_sts` tinyint NOT NULL DEFAULT '0' COMMENT '处理退款状态:(1.买家申请 2.卖家接受 3.买家发货 4.卖家收货 5.退款成功 -1.退款关闭)详情见ReturnMoneyStsType',
|
||
`buyer_reason` tinyint DEFAULT NULL COMMENT '申请原因(具体见BuyerReasonType)',
|
||
`buyer_desc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请说明',
|
||
`buyer_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系方式（退款时留下的手机号码）',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
`shop_img_urls` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ''商家/供应商文件凭证(逗号隔开)'',
|
||
`over_time` datetime DEFAULT NULL COMMENT '超时时间（超过该时间不处理，系统将自动处理）（保留字段）',
|
||
`reject_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '拒绝原因',
|
||
`seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卖家备注',
|
||
`shop_seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商家备注(移交供应商处理时保存)',
|
||
`handel_time` datetime DEFAULT NULL COMMENT '受理时间',
|
||
`supplier_time` datetime DEFAULT NULL COMMENT '移交供应商时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`receive_time` datetime DEFAULT NULL COMMENT '收货时间',
|
||
`close_time` datetime DEFAULT NULL COMMENT '关闭时间',
|
||
`decision_time` datetime DEFAULT NULL COMMENT '确定时间(确定退款时间)',
|
||
`refund_time` datetime DEFAULT NULL COMMENT '退款时间',
|
||
`supplier_id` bigint NOT NULL DEFAULT '0' COMMENT '供应商id',
|
||
`supplier_handle_status` tinyint DEFAULT '-1' COMMENT '供应商处理状态， -1.商家自行处理 0.待供应商处理 1.供应商已同意 2.供应商已拒绝',
|
||
`purchase_refund_amount` bigint DEFAULT '0' COMMENT '采购退款金额',
|
||
`pur_platform_refund_commission` bigint DEFAULT '0' COMMENT '采购的平台佣金退款金额',
|
||
`return_giveaway_ids` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款退回的赠品订单项ids',
|
||
`refund_actual_total` decimal(15,2) DEFAULT NULL COMMENT '当前退款的实付金额（实付金额减去不退回赠品的金额）',
|
||
`platform_intervention_status` tinyint NOT NULL DEFAULT '-1' COMMENT '平台介入状态 -1.没有介入 1.用户申请介入 2.平台同意介入 3.平台拒绝介入 5.平台同意退款成功',
|
||
`apply_intervention_reason` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户申请介入理由',
|
||
`apply_intervention_img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请介入的文件凭证(逗号隔开)',
|
||
`intervention_refund_type` tinyint DEFAULT NULL COMMENT '平台介入退款方式 1.商家承担 2.商家供应商承担（供应商发货订单） 3.不同意退款',
|
||
`after_intervention_refund_amount` bigint DEFAULT NULL COMMENT '平台介入前的退款金额',
|
||
`platform_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '平台留言',
|
||
`apply_intervention_time` datetime DEFAULT NULL COMMENT '申请介入时间',
|
||
`intervention_finish_time` datetime DEFAULT NULL COMMENT '介入完成时间',
|
||
PRIMARY KEY (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_item_id` (`order_item_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款记录信息'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_table FROM @create_order_refund_table;
|
||
EXECUTE create_order_refund_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_intervention_', i,
|
||
"
|
||
(
|
||
`refund_intervertion_id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`biz_id` bigint DEFAULT '0' COMMENT '商家/供应商/用户id',
|
||
`sys_type` tinyint DEFAULT NULL COMMENT '系统类型，0.普通用户 1.商家端 2.平台端 3.供应商端',
|
||
`voucher_desc` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '凭证说明',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
PRIMARY KEY (`refund_intervertion_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_biz_id` (`biz_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款介入记录'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_addr_', i,
|
||
"
|
||
(
|
||
`refund_addr_id` bigint NOT NULL AUTO_INCREMENT COMMENT '物流ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款号',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`delivery_company_id` bigint DEFAULT NULL COMMENT '物流公司ID',
|
||
`delivery_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流公司名称',
|
||
`delivery_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流单号',
|
||
`consignee_name` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人姓名',
|
||
`consignee_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人电话（顺丰快递需要）',
|
||
`consignee_telephone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人座机',
|
||
`consignee_post_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人邮政编码',
|
||
`consignee_addr` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人地址',
|
||
`sender_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发送人手机号码',
|
||
`sender_remarks` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '买家备注',
|
||
`imgs` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片凭证',
|
||
PRIMARY KEY (`refund_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_delivery_company_id` (`delivery_company_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户退货物流地址'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_settlement_', i,
|
||
"
|
||
(
|
||
`settlement_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '退款结算单据id',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`pay_id` bigint DEFAULT NULL COMMENT '订单支付单号',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`refund_id` bigint DEFAULT NULL COMMENT '退款单编号（本系统退款单号）',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`biz_refund_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信/支付宝退款单号（支付平台退款单号）',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式(1:微信支付 2支付宝支付)',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '退款状态(1:申请中 2：已完成 -1失败)',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`order_total_amount` bigint DEFAULT NULL COMMENT '订单总额',
|
||
`version` int DEFAULT '0' COMMENT '版本号',
|
||
PRIMARY KEY (`settlement_id`),
|
||
UNIQUE KEY `uni_refund_id` (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_pay_id` (`pay_id`),
|
||
KEY `idx_biz_refund_no` (`biz_refund_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='退款支付结算单据'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
# ============================== 采购订单 ==============================
|
||
SET i = 2;
|
||
WHILE i<64 DO
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_', i,
|
||
"(`purchase_order_id` bigint NOT NULL AUTO_INCREMENT COMMENT '采购id',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deliver_time` datetime DEFAULT NULL COMMENT '送达时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`supplier_id` bigint NOT NULL COMMENT '供应商id',
|
||
`total_amount` bigint NOT NULL COMMENT '总采购金额',
|
||
`total_stock` int NOT NULL COMMENT '总采购库存数量',
|
||
`actual_total_stock` int DEFAULT NULL COMMENT '实际总库存数量',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
|
||
`employee_id` bigint DEFAULT NULL COMMENT '操作员工id',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '配送类型 1:快递 2:自提 3：无需快递 4同城配送',
|
||
`purchase_order_addr_id` bigint DEFAULT NULL COMMENT '采购订单地址Id',
|
||
`voucher_img_urls` VARCHAR(1000) DEFAULT NULL COMMENT '付款凭证图片 多个图片逗号分隔',
|
||
`warehouse_id` bigint DEFAULT NULL COMMENT '仓库id',
|
||
`supplier_warehouse_id` bigint DEFAULT NULL COMMENT '供应商仓库id',
|
||
`stock_point_type` int DEFAULT NULL COMMENT '库存点类型(1:仓库, 2:门店)',
|
||
PRIMARY KEY (`purchase_order_id`),
|
||
KEY `idx_supplier_id` (`supplier_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_lang_', i,
|
||
"
|
||
(
|
||
`purchase_order_id` bigint NOT NULL,
|
||
`lang` tinyint NOT NULL,
|
||
`spu_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
|
||
PRIMARY KEY (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT 'id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`purchase_order_id` bigint DEFAULT NULL COMMENT '采购订单id',
|
||
`spu_id` bigint DEFAULT NULL COMMENT '商品id',
|
||
`sku_id` bigint DEFAULT NULL COMMENT 'sku id',
|
||
`spu_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品编码',
|
||
`party_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku编码',
|
||
`pic` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品图片',
|
||
`purchase_amount` bigint DEFAULT NULL COMMENT '采购金额',
|
||
`purchase_price` bigint DEFAULT NULL COMMENT '采购价',
|
||
`purchase_stock` int DEFAULT NULL COMMENT '采购库存数量',
|
||
`actual_stock` int DEFAULT NULL COMMENT '实际库存数量',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '订单项退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`be_delivered_num` int DEFAULT NULL COMMENT '0全部发货 其他数量为剩余待发货数量',
|
||
PRIMARY KEY (`purchase_item_id`),
|
||
KEY `idx_purchase_order_id` (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单项'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_lang_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT '订单项ID',
|
||
`lang` tinyint NOT NULL COMMENT '语言 1.中文 2.英文',
|
||
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
PRIMARY KEY (`purchase_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_addr_', i,
|
||
"
|
||
(
|
||
`purchase_order_addr_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`consignee` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收货人',
|
||
`province_id` bigint DEFAULT NULL COMMENT '省ID',
|
||
`province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省',
|
||
`city_id` bigint DEFAULT NULL COMMENT '城市ID',
|
||
`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
|
||
`area_id` bigint DEFAULT NULL COMMENT '区域ID',
|
||
`area` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
|
||
`addr` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
|
||
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机',
|
||
PRIMARY KEY (`purchase_order_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单配送地址'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_7;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
# ============================== 订单退款 ==============================
|
||
SET i = 32;
|
||
WHILE i<64 DO
|
||
SET @create_order_refund_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_', i,
|
||
"
|
||
(
|
||
`refund_id` bigint NOT NULL COMMENT '记录ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint NOT NULL COMMENT '店铺ID',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`order_id` bigint NOT NULL COMMENT '订单号',
|
||
`order_item_id` bigint NOT NULL DEFAULT '0' COMMENT '订单项ID(0:为全部订单项)',
|
||
`refund_count` int DEFAULT '0' COMMENT '退货数量(0:为全部订单项)',
|
||
`refund_score` bigint DEFAULT NULL COMMENT '退还积分',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`platform_refund_commission` bigint DEFAULT NULL COMMENT '平台佣金退款金额',
|
||
`platform_refund_amount` bigint DEFAULT NULL COMMENT '平台退款金额（退款时将这部分钱退回给平台，所以商家要扣除从平台这里获取的金额）',
|
||
`distribution_total_amount` bigint DEFAULT NULL COMMENT '退款单总分销金额',
|
||
`refund_type` tinyint NOT NULL DEFAULT '0' COMMENT '退款单类型（1:整单退款,2:单个物品退款）',
|
||
`apply_type` tinyint NOT NULL COMMENT '申请类型:1,仅退款,2退款退货',
|
||
`pay_sys_type` tinyint NOT NULL DEFAULT '0' COMMENT '支付系统类型 0默认1通联支付',
|
||
`is_received` tinyint DEFAULT NULL COMMENT '是否接收到商品(1:已收到,0:未收到)',
|
||
`close_type` tinyint DEFAULT NULL COMMENT '退款关闭原因(1.买家撤销退款 2.卖家拒绝退款 3.退款申请超时被系统关闭)',
|
||
`return_money_sts` tinyint NOT NULL DEFAULT '0' COMMENT '处理退款状态:(1.买家申请 2.卖家接受 3.买家发货 4.卖家收货 5.退款成功 -1.退款关闭)详情见ReturnMoneyStsType',
|
||
`buyer_reason` tinyint DEFAULT NULL COMMENT '申请原因(具体见BuyerReasonType)',
|
||
`buyer_desc` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请说明',
|
||
`buyer_mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系方式（退款时留下的手机号码）',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
`shop_img_urls` VARCHAR(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ''商家/供应商文件凭证(逗号隔开)'',
|
||
`over_time` datetime DEFAULT NULL COMMENT '超时时间（超过该时间不处理，系统将自动处理）（保留字段）',
|
||
`reject_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '拒绝原因',
|
||
`seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卖家备注',
|
||
`shop_seller_msg` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商家备注(移交供应商处理时保存)',
|
||
`handel_time` datetime DEFAULT NULL COMMENT '受理时间',
|
||
`supplier_time` datetime DEFAULT NULL COMMENT '移交供应商时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`receive_time` datetime DEFAULT NULL COMMENT '收货时间',
|
||
`close_time` datetime DEFAULT NULL COMMENT '关闭时间',
|
||
`decision_time` datetime DEFAULT NULL COMMENT '确定时间(确定退款时间)',
|
||
`refund_time` datetime DEFAULT NULL COMMENT '退款时间',
|
||
`supplier_id` bigint NOT NULL DEFAULT '0' COMMENT '供应商id',
|
||
`supplier_handle_status` tinyint DEFAULT '-1' COMMENT '供应商处理状态， -1.商家自行处理 0.待供应商处理 1.供应商已同意 2.供应商已拒绝',
|
||
`purchase_refund_amount` bigint DEFAULT '0' COMMENT '采购退款金额',
|
||
`pur_platform_refund_commission` bigint DEFAULT '0' COMMENT '采购的平台佣金退款金额',
|
||
`return_giveaway_ids` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '退款退回的赠品订单项ids',
|
||
`refund_actual_total` decimal(15,2) DEFAULT NULL COMMENT '当前退款的实付金额（实付金额减去不退回赠品的金额）',
|
||
`platform_intervention_status` tinyint NOT NULL DEFAULT '-1' COMMENT '平台介入状态 -1.没有介入 1.用户申请介入 2.平台同意介入 3.平台拒绝介入 5.平台同意退款成功',
|
||
`apply_intervention_reason` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户申请介入理由',
|
||
`apply_intervention_img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请介入的文件凭证(逗号隔开)',
|
||
`intervention_refund_type` tinyint DEFAULT NULL COMMENT '平台介入退款方式 1.商家承担 2.商家供应商承担（供应商发货订单） 3.不同意退款',
|
||
`after_intervention_refund_amount` bigint DEFAULT NULL COMMENT '平台介入前的退款金额',
|
||
`platform_message` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '平台留言',
|
||
`apply_intervention_time` datetime DEFAULT NULL COMMENT '申请介入时间',
|
||
`intervention_finish_time` datetime DEFAULT NULL COMMENT '介入完成时间',
|
||
PRIMARY KEY (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_order_item_id` (`order_item_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款记录信息'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_table FROM @create_order_refund_table;
|
||
EXECUTE create_order_refund_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_intervention_', i,
|
||
"
|
||
(
|
||
`refund_intervertion_id` bigint NOT NULL AUTO_INCREMENT COMMENT '记录ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`biz_id` bigint DEFAULT '0' COMMENT '商家/供应商/用户id',
|
||
`sys_type` tinyint DEFAULT NULL COMMENT '系统类型，0.普通用户 1.商家端 2.平台端 3.供应商端',
|
||
`voucher_desc` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '凭证说明',
|
||
`img_urls` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件凭证(逗号隔开)',
|
||
PRIMARY KEY (`refund_intervertion_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_biz_id` (`biz_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单退款介入记录'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_addr_', i,
|
||
"
|
||
(
|
||
`refund_addr_id` bigint NOT NULL AUTO_INCREMENT COMMENT '物流ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`refund_id` bigint NOT NULL COMMENT '退款号',
|
||
`user_id` bigint NOT NULL COMMENT '买家ID',
|
||
`delivery_company_id` bigint DEFAULT NULL COMMENT '物流公司ID',
|
||
`delivery_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流公司名称',
|
||
`delivery_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '物流单号',
|
||
`consignee_name` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人姓名',
|
||
`consignee_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人电话（顺丰快递需要）',
|
||
`consignee_telephone` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人座机',
|
||
`consignee_post_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人邮政编码',
|
||
`consignee_addr` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收件人地址',
|
||
`sender_mobile` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '发送人手机号码',
|
||
`sender_remarks` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '买家备注',
|
||
`imgs` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片凭证',
|
||
PRIMARY KEY (`refund_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`),
|
||
KEY `idx_refund_id` (`refund_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_delivery_company_id` (`delivery_company_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户退货物流地址'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS order_refund_settlement_', i,
|
||
"
|
||
(
|
||
`settlement_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '退款结算单据id',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`pay_id` bigint DEFAULT NULL COMMENT '订单支付单号',
|
||
`order_id` bigint DEFAULT NULL COMMENT '订单编号',
|
||
`refund_id` bigint DEFAULT NULL COMMENT '退款单编号（本系统退款单号）',
|
||
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
|
||
`biz_refund_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '微信/支付宝退款单号（支付平台退款单号）',
|
||
`pay_type` tinyint DEFAULT NULL COMMENT '支付方式(1:微信支付 2支付宝支付)',
|
||
`refund_status` tinyint DEFAULT NULL COMMENT '退款状态(1:申请中 2：已完成 -1失败)',
|
||
`refund_amount` bigint DEFAULT NULL COMMENT '退款金额',
|
||
`order_total_amount` bigint DEFAULT NULL COMMENT '订单总额',
|
||
`version` int DEFAULT '0' COMMENT '版本号',
|
||
PRIMARY KEY (`settlement_id`),
|
||
UNIQUE KEY `uni_refund_id` (`refund_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_pay_id` (`pay_id`),
|
||
KEY `idx_biz_refund_no` (`biz_refund_no`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='退款支付结算单据'
|
||
"
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
# ============================== 采购订单 ==============================
|
||
SET i = 2;
|
||
WHILE i<64 DO
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_', i,
|
||
"(`purchase_order_id` bigint NOT NULL AUTO_INCREMENT COMMENT '采购id',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deliver_time` datetime DEFAULT NULL COMMENT '送达时间',
|
||
`delivery_time` datetime DEFAULT NULL COMMENT '发货时间',
|
||
`supplier_id` bigint NOT NULL COMMENT '供应商id',
|
||
`total_amount` bigint NOT NULL COMMENT '总采购金额',
|
||
`total_stock` int NOT NULL COMMENT '总采购库存数量',
|
||
`actual_total_stock` int DEFAULT NULL COMMENT '实际总库存数量',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`remark` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
|
||
`employee_id` bigint DEFAULT NULL COMMENT '操作员工id',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '配送类型 1:快递 2:自提 3：无需快递 4同城配送',
|
||
`purchase_order_addr_id` bigint DEFAULT NULL COMMENT '采购订单地址Id',
|
||
`voucher_img_urls` VARCHAR(1000) DEFAULT NULL COMMENT '付款凭证图片 多个图片逗号分隔',
|
||
`warehouse_id` bigint DEFAULT NULL COMMENT '仓库id',
|
||
`supplier_warehouse_id` bigint DEFAULT NULL COMMENT '供应商仓库id',
|
||
`stock_point_type` int DEFAULT NULL COMMENT '库存点类型(1:仓库, 2:门店)',
|
||
PRIMARY KEY (`purchase_order_id`),
|
||
KEY `idx_supplier_id` (`supplier_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_lang_', i,
|
||
"
|
||
(
|
||
`purchase_order_id` bigint NOT NULL,
|
||
`lang` tinyint NOT NULL,
|
||
`spu_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
|
||
PRIMARY KEY (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT 'id',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`purchase_order_id` bigint DEFAULT NULL COMMENT '采购订单id',
|
||
`spu_id` bigint DEFAULT NULL COMMENT '商品id',
|
||
`sku_id` bigint DEFAULT NULL COMMENT 'sku id',
|
||
`spu_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品编码',
|
||
`party_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku编码',
|
||
`pic` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品图片',
|
||
`purchase_amount` bigint DEFAULT NULL COMMENT '采购金额',
|
||
`purchase_price` bigint DEFAULT NULL COMMENT '采购价',
|
||
`purchase_stock` int DEFAULT NULL COMMENT '采购库存数量',
|
||
`actual_stock` int DEFAULT NULL COMMENT '实际库存数量',
|
||
`delivery_type` tinyint DEFAULT NULL COMMENT '订单项退款状态（1:申请退款 2:退款成功 3:部分退款成功 4:退款失败）',
|
||
`status` tinyint DEFAULT NULL COMMENT '状态 0:已作废 1:待入库 2:已发货 3.部分入库 5:已完成',
|
||
`be_delivered_num` int DEFAULT NULL COMMENT '0全部发货 其他数量为剩余待发货数量',
|
||
PRIMARY KEY (`purchase_item_id`),
|
||
KEY `idx_purchase_order_id` (`purchase_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单项'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_item_lang_', i,
|
||
"
|
||
(
|
||
`purchase_item_id` bigint NOT NULL COMMENT '订单项ID',
|
||
`lang` tinyint NOT NULL COMMENT '语言 1.中文 2.英文',
|
||
`spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '商品名称',
|
||
`sku_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'sku名称',
|
||
PRIMARY KEY (`purchase_item_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单项-国际化'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'CREATE TABLE IF NOT EXISTS purchase_order_addr_', i,
|
||
"
|
||
(
|
||
`purchase_order_addr_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`shop_id` bigint DEFAULT NULL COMMENT '店铺ID',
|
||
`consignee` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '收货人',
|
||
`province_id` bigint DEFAULT NULL COMMENT '省ID',
|
||
`province` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '省',
|
||
`city_id` bigint DEFAULT NULL COMMENT '城市ID',
|
||
`city` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '城市',
|
||
`area_id` bigint DEFAULT NULL COMMENT '区域ID',
|
||
`area` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '区',
|
||
`addr` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地址',
|
||
`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机',
|
||
PRIMARY KEY (`purchase_order_addr_id`),
|
||
KEY `idx_shop_id` (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='采购订单配送地址'
|
||
"
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
|
||
# ============================== 4.统计订单数据到各个分表 ==============================
|
||
# 请确保上述1-3执行成功后再执行此sql
|
||
USE tmerclub_order_0;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i,j INT;
|
||
SET i = 0;
|
||
SET j = 0;
|
||
WHILE i<64 DO
|
||
SET @create_refund_info_table = CONCAT(
|
||
'INSERT INTO order_refund_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_refund_info_table FROM @create_refund_info_table;
|
||
EXECUTE create_refund_info_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'INSERT INTO order_refund_intervention_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_intervention` WHERE (SUBSTRING(refund_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(refund_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'INSERT INTO order_refund_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_addr` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'INSERT INTO order_refund_settlement_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_settlement` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'INSERT INTO purchase_order_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_lang` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item_lang` WHERE (SUBSTRING(purchase_item_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_item_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'INSERT INTO purchase_order_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_addr` WHERE (SUBSTRING(purchase_order_addr_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_addr_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_1;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i,j INT;
|
||
SET i = 0;
|
||
SET j = 1;
|
||
WHILE i<64 DO
|
||
SET @create_refund_info_table = CONCAT(
|
||
'INSERT INTO order_refund_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_refund_info_table FROM @create_refund_info_table;
|
||
EXECUTE create_refund_info_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'INSERT INTO order_refund_intervention_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_intervention` WHERE (SUBSTRING(refund_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(refund_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'INSERT INTO order_refund_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_addr` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'INSERT INTO order_refund_settlement_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_settlement` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'INSERT INTO purchase_order_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_lang` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item_lang` WHERE (SUBSTRING(purchase_item_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_item_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'INSERT INTO purchase_order_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_addr` WHERE (SUBSTRING(purchase_order_addr_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_addr_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_2;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i,j INT;
|
||
SET i = 0;
|
||
SET j = 2;
|
||
WHILE i<64 DO
|
||
SET @create_refund_info_table = CONCAT(
|
||
'INSERT INTO order_refund_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_refund_info_table FROM @create_refund_info_table;
|
||
EXECUTE create_refund_info_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'INSERT INTO order_refund_intervention_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_intervention` WHERE (SUBSTRING(refund_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(refund_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'INSERT INTO order_refund_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_addr` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'INSERT INTO order_refund_settlement_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_settlement` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'INSERT INTO purchase_order_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_lang` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item_lang` WHERE (SUBSTRING(purchase_item_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_item_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'INSERT INTO purchase_order_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_addr` WHERE (SUBSTRING(purchase_order_addr_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_addr_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_3;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i,j INT;
|
||
SET i = 0;
|
||
SET j = 3;
|
||
WHILE i<64 DO
|
||
SET @create_refund_info_table = CONCAT(
|
||
'INSERT INTO order_refund_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_refund_info_table FROM @create_refund_info_table;
|
||
EXECUTE create_refund_info_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'INSERT INTO order_refund_intervention_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_intervention` WHERE (SUBSTRING(refund_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(refund_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'INSERT INTO order_refund_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_addr` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'INSERT INTO order_refund_settlement_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_settlement` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'INSERT INTO purchase_order_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_lang` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item_lang` WHERE (SUBSTRING(purchase_item_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_item_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'INSERT INTO purchase_order_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_addr` WHERE (SUBSTRING(purchase_order_addr_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_addr_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_4;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i,j INT;
|
||
SET i = 0;
|
||
SET j = 4;
|
||
WHILE i<64 DO
|
||
SET @create_refund_info_table = CONCAT(
|
||
'INSERT INTO order_refund_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_refund_info_table FROM @create_refund_info_table;
|
||
EXECUTE create_refund_info_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'INSERT INTO order_refund_intervention_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_intervention` WHERE (SUBSTRING(refund_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(refund_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'INSERT INTO order_refund_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_addr` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'INSERT INTO order_refund_settlement_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_settlement` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'INSERT INTO purchase_order_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_lang` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item_lang` WHERE (SUBSTRING(purchase_item_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_item_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'INSERT INTO purchase_order_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_addr` WHERE (SUBSTRING(purchase_order_addr_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_addr_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_5;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i,j INT;
|
||
SET i = 0;
|
||
SET j = 5;
|
||
WHILE i<64 DO
|
||
SET @create_refund_info_table = CONCAT(
|
||
'INSERT INTO order_refund_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_refund_info_table FROM @create_refund_info_table;
|
||
EXECUTE create_refund_info_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'INSERT INTO order_refund_intervention_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_intervention` WHERE (SUBSTRING(refund_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(refund_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'INSERT INTO order_refund_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_addr` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'INSERT INTO order_refund_settlement_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_settlement` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'INSERT INTO purchase_order_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_lang` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item_lang` WHERE (SUBSTRING(purchase_item_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_item_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'INSERT INTO purchase_order_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_addr` WHERE (SUBSTRING(purchase_order_addr_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_addr_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_6;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i,j INT;
|
||
SET i = 0;
|
||
SET j = 6;
|
||
WHILE i<64 DO
|
||
SET @create_refund_info_table = CONCAT(
|
||
'INSERT INTO order_refund_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_refund_info_table FROM @create_refund_info_table;
|
||
EXECUTE create_refund_info_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'INSERT INTO order_refund_intervention_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_intervention` WHERE (SUBSTRING(refund_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(refund_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'INSERT INTO order_refund_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_addr` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'INSERT INTO order_refund_settlement_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_settlement` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'INSERT INTO purchase_order_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_lang` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item_lang` WHERE (SUBSTRING(purchase_item_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_item_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'INSERT INTO purchase_order_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_addr` WHERE (SUBSTRING(purchase_order_addr_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_addr_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_7;
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i,j INT;
|
||
SET i = 0;
|
||
SET j = 7;
|
||
WHILE i<64 DO
|
||
SET @create_refund_info_table = CONCAT(
|
||
'INSERT INTO order_refund_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_refund_info_table FROM @create_refund_info_table;
|
||
EXECUTE create_refund_info_table;
|
||
|
||
SET @create_order_refund_intervention_table = CONCAT(
|
||
'INSERT INTO order_refund_intervention_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_intervention` WHERE (SUBSTRING(refund_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(refund_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_intervention_table FROM @create_order_refund_intervention_table;
|
||
EXECUTE create_order_refund_intervention_table;
|
||
|
||
SET @create_order_refund_addr_table = CONCAT(
|
||
'INSERT INTO order_refund_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_addr` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_addr_table FROM @create_order_refund_addr_table;
|
||
EXECUTE create_order_refund_addr_table;
|
||
|
||
SET @create_order_refund_settlement_table = CONCAT(
|
||
'INSERT INTO order_refund_settlement_', i, '
|
||
(SELECT * FROM tmerclub_order.`order_refund_settlement` WHERE (SUBSTRING(user_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(user_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_order_refund_settlement_table FROM @create_order_refund_settlement_table;
|
||
EXECUTE create_order_refund_settlement_table;
|
||
|
||
SET @create_purchase_order_table = CONCAT(
|
||
'INSERT INTO purchase_order_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_table FROM @create_purchase_order_table;
|
||
EXECUTE create_purchase_order_table;
|
||
|
||
SET @create_purchase_order_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_lang` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_lang_table FROM @create_purchase_order_lang_table;
|
||
EXECUTE create_purchase_order_lang_table;
|
||
|
||
SET @create_purchase_order_item_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item` WHERE (SUBSTRING(purchase_order_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_table FROM @create_purchase_order_item_table;
|
||
EXECUTE create_purchase_order_item_table;
|
||
|
||
SET @create_purchase_order_item_lang_table = CONCAT(
|
||
'INSERT INTO purchase_order_item_lang_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_item_lang` WHERE (SUBSTRING(purchase_item_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_item_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_item_lang_table FROM @create_purchase_order_item_lang_table;
|
||
EXECUTE create_purchase_order_item_lang_table;
|
||
|
||
SET @create_purchase_order_addr_table = CONCAT(
|
||
'INSERT INTO purchase_order_addr_', i, '
|
||
(SELECT * FROM tmerclub_order.`purchase_order_addr` WHERE (SUBSTRING(purchase_order_addr_id, -3) + 0) % 8 = ', j, '
|
||
AND ((SUBSTRING(purchase_order_addr_id, -3) + 0) DIV 8) % 64 = ', i, ' )'
|
||
);
|
||
PREPARE create_purchase_order_addr_table FROM @create_purchase_order_addr_table;
|
||
EXECUTE create_purchase_order_addr_table;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
|
||
# ============================== 5.修改nacos配置 ==============================
|
||
USE `tmerclub_nacos`;
|
||
# ① 修改tmerclub-order.yml配置，注意修改mongodb的host,将原本的mysql连接改为连接shardingSphere-proxy
|
||
UPDATE config_info
|
||
SET content= '# 数据源\nspring:\n datasource:\n url: jdbc:mysql://${MYSQL_HOST:192.168.193.128}:${MYSQL_PORT:3307}/${MYSQL_DATABASE:tmerclub_order}?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true&allowPublicKeyRetrieval=true&useAffectedRows=true\n username: ${MYSQL_USERNAME:sharding}\n password: ${MYSQL_PASSWORD:hn02le.34lkdLKD}\n data:\n mongodb:\n host: 192.168.193.128\n port: 27017\n database: tmerclub\n username: tmerclub\n password: tmerclub\n authentication-database: tmerclub\n\n\ntmerclub:\n order:\n thread:\n coreSize: 0\n maxSize: 200\n keepAliveTime: 60\n\nmanagement:\n health:\n db:\n enabled: false'
|
||
WHERE data_id = 'tmerclub-order.yml';
|
||
|
||
# ② 修改tmerclub-payment.yml配置，注意修改domainUrl回调域名,将原本的mysql连接改为连接shardingSphere-proxy
|
||
UPDATE config_info
|
||
SET content= '# 数据源\nspring:\n datasource:\n url: jdbc:mysql://${MYSQL_HOST:192.168.193.128}:${MYSQL_PORT:3307}/${MYSQL_DATABASE:tmerclub_payment}?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8&allowMultiQueries=true&allowPublicKeyRetrieval=true&useAffectedRows=true\n username: ${MYSQL_USERNAME:sharding}\n password: ${MYSQL_PASSWORD:hn02le.34lkdLKD}\n\napplication:\n domainUrl: https://cloud-api.mall4j.com/tmerclub_payment'
|
||
WHERE data_id = 'tmerclub-payment.yml';
|
||
|
||
# ③ 部署shardingSphere中间件，具体参考tmerclub-doc项目下文档/src/branch/master/开发环境搭建/中间件docker-compose一键安装中新增的shardingSphere内容
|
||
|
||
# ④ 修改application.yml配置
|
||
# 1.将原配置中seata配置删除，如下部分删除
|
||
# seata:
|
||
# config:
|
||
# type: nacos
|
||
# nacos:
|
||
# namespace: 05b1150e-9306-474c-90e1-ed4852c21475
|
||
# dataId: "seataServer.properties"
|
||
# server-addr: ${spring.cloud.nacos.discovery.server-addr}
|
||
# password: ${spring.cloud.nacos.discovery.password}
|
||
# username: ${spring.cloud.nacos.discovery.username}
|
||
# registry:
|
||
# type: nacos
|
||
# nacos:
|
||
# server-addr: ${spring.cloud.nacos.discovery.server-addr}
|
||
# password: ${spring.cloud.nacos.discovery.password}
|
||
# username: ${spring.cloud.nacos.discovery.username}
|
||
# namespace: ${seata.config.nacos.namespace}
|
||
|
||
# 2.新增新的seata配置，注意修改ip
|
||
# seata:
|
||
# service:
|
||
# grouplist:
|
||
# default: 192.168.193.128:8091
|
||
# vgroupMapping:
|
||
# default_tx_group: default
|
||
|
||
|
||
# ⑤ 删除原seata在nacos中的配置
|
||
DELETE FROM `config_info` WHERE data_id = 'seataServer.properties' AND group_id = 'SEATA_GROUP';
|
||
DELETE FROM `tenant_info` WHERE tenant_id = '4b70485d-72dd-44df-a76a-7a3f578a3001';
|
||
|
||
# ⑥ 更新seata表，更新结束后重新部署seata，升级版本
|
||
# 具体参考tmerclub-doc项目中 开发环境搭建--中间件docker-compose一键安装中seata的application.yml配置并且更新docker-compose.yml中seata部分
|
||
# 重新部署一个seata 2.0.0版本
|
||
USE `tmerclub_seata`;
|
||
DROP TABLE IF EXISTS `global_table`;
|
||
CREATE TABLE IF NOT EXISTS `global_table`
|
||
(
|
||
`xid` VARCHAR(128) NOT NULL,
|
||
`transaction_id` BIGINT,
|
||
`status` TINYINT NOT NULL,
|
||
`application_id` VARCHAR(32),
|
||
`transaction_service_group` VARCHAR(32),
|
||
`transaction_name` VARCHAR(128),
|
||
`timeout` INT,
|
||
`begin_time` BIGINT,
|
||
`application_data` VARCHAR(2000),
|
||
`gmt_create` DATETIME,
|
||
`gmt_modified` DATETIME,
|
||
PRIMARY KEY (`xid`),
|
||
KEY `idx_status_gmt_modified` (`status` , `gmt_modified`),
|
||
KEY `idx_transaction_id` (`transaction_id`)
|
||
) ENGINE = INNODB
|
||
DEFAULT CHARSET = utf8mb4;
|
||
|
||
-- the table to store BranchSession data
|
||
DROP TABLE IF EXISTS `branch_table`;
|
||
CREATE TABLE IF NOT EXISTS `branch_table`
|
||
(
|
||
`branch_id` BIGINT NOT NULL,
|
||
`xid` VARCHAR(128) NOT NULL,
|
||
`transaction_id` BIGINT,
|
||
`resource_group_id` VARCHAR(32),
|
||
`resource_id` VARCHAR(256),
|
||
`branch_type` VARCHAR(8),
|
||
`status` TINYINT,
|
||
`client_id` VARCHAR(64),
|
||
`application_data` VARCHAR(2000),
|
||
`gmt_create` DATETIME(6),
|
||
`gmt_modified` DATETIME(6),
|
||
PRIMARY KEY (`branch_id`),
|
||
KEY `idx_xid` (`xid`)
|
||
) ENGINE = INNODB
|
||
DEFAULT CHARSET = utf8mb4;
|
||
|
||
-- the table to store lock data
|
||
DROP TABLE IF EXISTS `lock_table`;
|
||
CREATE TABLE `lock_table`
|
||
(
|
||
`row_key` VARCHAR(128) NOT NULL,
|
||
`xid` VARCHAR(128),
|
||
`transaction_id` BIGINT,
|
||
`branch_id` BIGINT NOT NULL,
|
||
`resource_id` VARCHAR(256),
|
||
`table_name` VARCHAR(32),
|
||
`pk` VARCHAR(36),
|
||
`status` TINYINT NOT NULL DEFAULT '0' COMMENT '0:locked ,1:rollbacking',
|
||
`gmt_create` DATETIME,
|
||
`gmt_modified` DATETIME,
|
||
PRIMARY KEY (`row_key`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_branch_id` (`branch_id`),
|
||
KEY `idx_xid` (`xid`)
|
||
) ENGINE = INNODB
|
||
DEFAULT CHARSET = utf8mb4;
|
||
|
||
CREATE TABLE IF NOT EXISTS `distributed_lock`
|
||
(
|
||
`lock_key` CHAR(20) NOT NULL,
|
||
`lock_value` VARCHAR(20) NOT NULL,
|
||
`expire` BIGINT,
|
||
PRIMARY KEY (`lock_key`)
|
||
) ENGINE = INNODB
|
||
DEFAULT CHARSET = utf8mb4;
|
||
|
||
INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('AsyncCommitting', ' ', 0);
|
||
INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('RetryCommitting', ' ', 0);
|
||
INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('RetryRollbacking', ' ', 0);
|
||
INSERT INTO `distributed_lock` (lock_key, lock_value, expire) VALUES ('TxTimeoutCheck', ' ', 0);
|
||
|
||
|
||
# ============================== 6.删除总表中数据 ==============================
|
||
# 确保6.7.执行 成功 执行一次后，检查是否数据已经同步到各个分表中去，再执行下面的sql
|
||
USE tmerclub_order;
|
||
DROP TABLE IF EXISTS `order_refund`;
|
||
DROP TABLE IF EXISTS `order_refund_intervention`;
|
||
DROP TABLE IF EXISTS `order_refund_addr`;
|
||
DROP TABLE IF EXISTS `order_refund_settlement`;
|
||
DROP TABLE IF EXISTS `purchase_order`;
|
||
DROP TABLE IF EXISTS `purchase_order_lang`;
|
||
DROP TABLE IF EXISTS `purchase_order_item`;
|
||
DROP TABLE IF EXISTS `purchase_order_item_lang`;
|
||
DROP TABLE IF EXISTS `purchase_order_addr`;
|
||
|
||
|
||
|
||
|
||
|
||
# ============================== 7.移除order_item_lang多余国际化信息 ==============================
|
||
|
||
USE tmerclub_order_0;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @delete_lang_data = CONCAT(
|
||
'delete from order_item_lang_', i,' WHERE lang = 2'
|
||
);
|
||
PREPARE delete_lang_data FROM @delete_lang_data;
|
||
EXECUTE delete_lang_data;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
|
||
USE tmerclub_order_1;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @delete_lang_data = CONCAT(
|
||
'delete from order_item_lang_', i,' WHERE lang = 2'
|
||
);
|
||
PREPARE delete_lang_data FROM @delete_lang_data;
|
||
EXECUTE delete_lang_data;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_2;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @delete_lang_data = CONCAT(
|
||
'delete from order_item_lang_', i,' WHERE lang = 2'
|
||
);
|
||
PREPARE delete_lang_data FROM @delete_lang_data;
|
||
EXECUTE delete_lang_data;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_3;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @delete_lang_data = CONCAT(
|
||
'delete from order_item_lang_', i,' WHERE lang = 2'
|
||
);
|
||
PREPARE delete_lang_data FROM @delete_lang_data;
|
||
EXECUTE delete_lang_data;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_4;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @delete_lang_data = CONCAT(
|
||
'delete from order_item_lang_', i,' WHERE lang = 2'
|
||
);
|
||
PREPARE delete_lang_data FROM @delete_lang_data;
|
||
EXECUTE delete_lang_data;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_5;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @delete_lang_data = CONCAT(
|
||
'delete from order_item_lang_', i,' WHERE lang = 2'
|
||
);
|
||
PREPARE delete_lang_data FROM @delete_lang_data;
|
||
EXECUTE delete_lang_data;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_6;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @delete_lang_data = CONCAT(
|
||
'delete from order_item_lang_', i,' WHERE lang = 2'
|
||
);
|
||
PREPARE delete_lang_data FROM @delete_lang_data;
|
||
EXECUTE delete_lang_data;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_7;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @delete_lang_data = CONCAT(
|
||
'delete from order_item_lang_', i,' WHERE lang = 2'
|
||
);
|
||
PREPARE delete_lang_data FROM @delete_lang_data;
|
||
EXECUTE delete_lang_data;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
|
||
|
||
# ======================================= 8.改变主键 =======================================
|
||
|
||
|
||
USE tmerclub_order_0;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @change_item_key = CONCAT(
|
||
'ALTER TABLE order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`order_item_id`)'
|
||
);
|
||
PREPARE change_item_key FROM @change_item_key;
|
||
EXECUTE change_item_key;
|
||
|
||
SET @change_purchase_item_key = CONCAT(
|
||
'ALTER TABLE purchase_order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_item_id`)'
|
||
);
|
||
PREPARE change_purchase_item_key FROM @change_purchase_item_key;
|
||
EXECUTE change_purchase_item_key;
|
||
|
||
SET @change_purchase_order_key = CONCAT(
|
||
'ALTER TABLE purchase_order_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_order_id`)'
|
||
);
|
||
PREPARE change_purchase_order_key FROM @change_purchase_order_key;
|
||
EXECUTE change_purchase_order_key;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
|
||
USE tmerclub_order_1;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @change_item_key = CONCAT(
|
||
'ALTER TABLE order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`order_item_id`)'
|
||
);
|
||
PREPARE change_item_key FROM @change_item_key;
|
||
EXECUTE change_item_key;
|
||
|
||
SET @change_purchase_item_key = CONCAT(
|
||
'ALTER TABLE purchase_order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_item_id`)'
|
||
);
|
||
PREPARE change_purchase_item_key FROM @change_purchase_item_key;
|
||
EXECUTE change_purchase_item_key;
|
||
|
||
SET @change_purchase_order_key = CONCAT(
|
||
'ALTER TABLE purchase_order_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_order_id`)'
|
||
);
|
||
PREPARE change_purchase_order_key FROM @change_purchase_order_key;
|
||
EXECUTE change_purchase_order_key;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_2;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @change_item_key = CONCAT(
|
||
'ALTER TABLE order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`order_item_id`)'
|
||
);
|
||
PREPARE change_item_key FROM @change_item_key;
|
||
EXECUTE change_item_key;
|
||
|
||
SET @change_purchase_item_key = CONCAT(
|
||
'ALTER TABLE purchase_order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_item_id`)'
|
||
);
|
||
PREPARE change_purchase_item_key FROM @change_purchase_item_key;
|
||
EXECUTE change_purchase_item_key;
|
||
|
||
SET @change_purchase_order_key = CONCAT(
|
||
'ALTER TABLE purchase_order_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_order_id`)'
|
||
);
|
||
PREPARE change_purchase_order_key FROM @change_purchase_order_key;
|
||
EXECUTE change_purchase_order_key;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_3;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @change_item_key = CONCAT(
|
||
'ALTER TABLE order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`order_item_id`)'
|
||
);
|
||
PREPARE change_item_key FROM @change_item_key;
|
||
EXECUTE change_item_key;
|
||
|
||
SET @change_purchase_item_key = CONCAT(
|
||
'ALTER TABLE purchase_order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_item_id`)'
|
||
);
|
||
PREPARE change_purchase_item_key FROM @change_purchase_item_key;
|
||
EXECUTE change_purchase_item_key;
|
||
|
||
SET @change_purchase_order_key = CONCAT(
|
||
'ALTER TABLE purchase_order_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_order_id`)'
|
||
);
|
||
PREPARE change_purchase_order_key FROM @change_purchase_order_key;
|
||
EXECUTE change_purchase_order_key;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_4;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @change_item_key = CONCAT(
|
||
'ALTER TABLE order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`order_item_id`)'
|
||
);
|
||
PREPARE change_item_key FROM @change_item_key;
|
||
EXECUTE change_item_key;
|
||
|
||
SET @change_purchase_item_key = CONCAT(
|
||
'ALTER TABLE purchase_order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_item_id`)'
|
||
);
|
||
PREPARE change_purchase_item_key FROM @change_purchase_item_key;
|
||
EXECUTE change_purchase_item_key;
|
||
|
||
SET @change_purchase_order_key = CONCAT(
|
||
'ALTER TABLE purchase_order_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_order_id`)'
|
||
);
|
||
PREPARE change_purchase_order_key FROM @change_purchase_order_key;
|
||
EXECUTE change_purchase_order_key;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_5;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @change_item_key = CONCAT(
|
||
'ALTER TABLE order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`order_item_id`)'
|
||
);
|
||
PREPARE change_item_key FROM @change_item_key;
|
||
EXECUTE change_item_key;
|
||
|
||
SET @change_purchase_item_key = CONCAT(
|
||
'ALTER TABLE purchase_order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_item_id`)'
|
||
);
|
||
PREPARE change_purchase_item_key FROM @change_purchase_item_key;
|
||
EXECUTE change_purchase_item_key;
|
||
|
||
SET @change_purchase_order_key = CONCAT(
|
||
'ALTER TABLE purchase_order_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_order_id`)'
|
||
);
|
||
PREPARE change_purchase_order_key FROM @change_purchase_order_key;
|
||
EXECUTE change_purchase_order_key;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_6;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @change_item_key = CONCAT(
|
||
'ALTER TABLE order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`order_item_id`)'
|
||
);
|
||
PREPARE change_item_key FROM @change_item_key;
|
||
EXECUTE change_item_key;
|
||
|
||
SET @change_purchase_item_key = CONCAT(
|
||
'ALTER TABLE purchase_order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_item_id`)'
|
||
);
|
||
PREPARE change_purchase_item_key FROM @change_purchase_item_key;
|
||
EXECUTE change_purchase_item_key;
|
||
|
||
SET @change_purchase_order_key = CONCAT(
|
||
'ALTER TABLE purchase_order_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_order_id`)'
|
||
);
|
||
PREPARE change_purchase_order_key FROM @change_purchase_order_key;
|
||
EXECUTE change_purchase_order_key;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
USE tmerclub_order_7;
|
||
|
||
DELIMITER //
|
||
DROP PROCEDURE IF EXISTS createTable//
|
||
CREATE PROCEDURE createTable()
|
||
BEGIN
|
||
DECLARE i INT;
|
||
SET i = 0;
|
||
WHILE i<64 DO
|
||
SET @change_item_key = CONCAT(
|
||
'ALTER TABLE order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`order_item_id`)'
|
||
);
|
||
PREPARE change_item_key FROM @change_item_key;
|
||
EXECUTE change_item_key;
|
||
|
||
SET @change_purchase_item_key = CONCAT(
|
||
'ALTER TABLE purchase_order_item_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_item_id`)'
|
||
);
|
||
PREPARE change_purchase_item_key FROM @change_purchase_item_key;
|
||
EXECUTE change_purchase_item_key;
|
||
|
||
SET @change_purchase_order_key = CONCAT(
|
||
'ALTER TABLE purchase_order_lang_', i,' DROP PRIMARY KEY, ADD PRIMARY KEY (`purchase_order_id`)'
|
||
);
|
||
PREPARE change_purchase_order_key FROM @change_purchase_order_key;
|
||
EXECUTE change_purchase_order_key;
|
||
|
||
SET i = i+1;
|
||
END WHILE;
|
||
END//
|
||
CALL createTable()//
|
||
DELIMITER ;
|
||
|
||
|
||
|
||
# ======================================= 9.移除多余的undolog =======================================
|
||
|
||
USE tmerclub_order_0; DROP TABLE IF EXISTS `undo_log`;
|
||
USE tmerclub_order_1; DROP TABLE IF EXISTS `undo_log`;
|
||
USE tmerclub_order_2; DROP TABLE IF EXISTS `undo_log`;
|
||
USE tmerclub_order_3; DROP TABLE IF EXISTS `undo_log`;
|
||
USE tmerclub_order_4; DROP TABLE IF EXISTS `undo_log`;
|
||
USE tmerclub_order_5; DROP TABLE IF EXISTS `undo_log`;
|
||
USE tmerclub_order_6; DROP TABLE IF EXISTS `undo_log`;
|
||
USE tmerclub_order_7; DROP TABLE IF EXISTS `undo_log`;
|
||
|
||
USE tmerclub_payment;
|
||
DROP TABLE IF EXISTS `undo_log`;
|
||
|
||
CREATE TABLE `undo_log` (
|
||
`id` bigint NOT NULL AUTO_INCREMENT,
|
||
`branch_id` bigint NOT NULL,
|
||
`xid` varchar(100) NOT NULL,
|
||
`context` varchar(128) NOT NULL,
|
||
`rollback_info` longblob NOT NULL,
|
||
`log_status` int NOT NULL,
|
||
`log_created` datetime NOT NULL,
|
||
`log_modified` datetime NOT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `ux_undo_log` (`xid`,`branch_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
||
|
||
USE tmerclub_payment_0; DROP TABLE IF EXISTS `undo_log`;
|
||
USE tmerclub_payment_1; DROP TABLE IF EXISTS `undo_log`;
|
||
USE tmerclub_payment_2; DROP TABLE IF EXISTS `undo_log`;
|
||
USE tmerclub_payment_3; DROP TABLE IF EXISTS `undo_log`;
|
||
USE tmerclub_payment_4; DROP TABLE IF EXISTS `undo_log`;
|
||
USE tmerclub_payment_5; DROP TABLE IF EXISTS `undo_log`;
|
||
USE tmerclub_payment_6; DROP TABLE IF EXISTS `undo_log`;
|
||
USE tmerclub_payment_7; DROP TABLE IF EXISTS `undo_log`; |