163 lines
11 KiB
SQL
163 lines
11 KiB
SQL
-- ============================================================
|
||
-- 企油通对接 · 退款 / 发票 / 司机管理
|
||
-- 生成：2026-09-14
|
||
-- ------------------------------------------------------------
|
||
-- 执行顺序：qyt_migration.sql -> _channel -> _enterprise -> _waybill_status -> 本文件
|
||
-- 本地库执行前请先确认上面四个已执行。
|
||
-- ============================================================
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 1. 订单退款状态
|
||
-- refund_status：0 未退款 / 1 退款中 / 2 已退款 / 3 退款失败(可重新发起) / 4 待人工确认
|
||
-- 4 的含义：7.16 请求已发出但结果未知（超时/响应异常）。7.16 没有幂等键，
|
||
-- 这种情况绝不能自动重试，必须先确认上游到底扣没扣，否则可能重复调低额度。
|
||
-- ------------------------------------------------------------
|
||
ALTER TABLE `yoshop_partners_order`
|
||
ADD COLUMN `refund_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '退款状态 0未退款 1退款中 2已退款 3退款失败 4待人工确认' AFTER `qyt_retry_num`,
|
||
ADD COLUMN `refund_amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '已退款金额(元)' AFTER `refund_status`,
|
||
ADD COLUMN `refund_time` int(11) NOT NULL DEFAULT 0 COMMENT '退款成功时间' AFTER `refund_amount`,
|
||
ADD COLUMN `refund_text` varchar(255) NOT NULL DEFAULT '' COMMENT '退款结果说明' AFTER `refund_time`,
|
||
ADD INDEX `idx_refund_status` (`refund_status`),
|
||
ADD INDEX `idx_id_card` (`id_card`);
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 2. 退款流水（每次发起一行，失败后重新发起会新增一行）
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE `yoshop_qyt_refund` (
|
||
`refund_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`refund_no` varchar(64) NOT NULL DEFAULT '' COMMENT '退款单号(同时写入7.16 operation_reason,确认时据此在7.13流水里找)',
|
||
`order_id` int(11) NOT NULL DEFAULT 0 COMMENT 'yoshop_partners_order.order_id',
|
||
`partners_id` int(11) NOT NULL DEFAULT 0 COMMENT '合作商ID',
|
||
`qyt_enterprise_id` int(11) NOT NULL DEFAULT 0 COMMENT '企油通企业ID',
|
||
`source` varchar(10) NOT NULL DEFAULT '' COMMENT '发起来源 admin后台 / api下单平台',
|
||
`operator` varchar(64) NOT NULL DEFAULT '' COMMENT '操作人(后台账号 / 合作商appKey)',
|
||
`reason` varchar(200) NOT NULL DEFAULT '' COMMENT '退款原因',
|
||
`amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '退款金额(元)',
|
||
`quota_id` varchar(32) NOT NULL DEFAULT '' COMMENT '被调低的凭证ID(7.17 quota_id)',
|
||
`total_before` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '调额前凭证总额度(元)',
|
||
`available_before` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '调额前凭证可用余额(元)',
|
||
`total_after` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '确认时凭证总额度(元)',
|
||
`available_after` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '确认时凭证可用余额(元)',
|
||
`status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1处理中 2成功 3失败 4待人工确认',
|
||
`message` varchar(255) NOT NULL DEFAULT '' COMMENT '结果说明',
|
||
`response` text COMMENT '上游响应(已脱敏)',
|
||
`store_id` int(11) NOT NULL DEFAULT 0 COMMENT '商城ID',
|
||
`create_time` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
|
||
`update_time` int(11) NOT NULL DEFAULT 0 COMMENT '更新时间',
|
||
PRIMARY KEY (`refund_id`),
|
||
UNIQUE KEY `uk_refund_no` (`refund_no`),
|
||
KEY `idx_order` (`order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='企油通退款流水(7.17查凭证+7.16调低额度)';
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 3. 企业发票（7.14）
|
||
-- 7.14 的粒度是「企业 + 通道 + 月份（+ 司机手机号）」，返回的发票地址与订单
|
||
-- 之间没有对应关系，因此按这个维度存；订单按「企业 + 消费月份 + 司机」关联展示。
|
||
-- mobile 为空串表示该企业该月全量。
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE `yoshop_qyt_invoice` (
|
||
`invoice_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`qyt_enterprise_id` int(11) NOT NULL DEFAULT 0 COMMENT '企油通企业ID',
|
||
`business_license` varchar(64) NOT NULL DEFAULT '' COMMENT '统一社会信用代码',
|
||
`passageway` varchar(20) NOT NULL DEFAULT '' COMMENT '通道 alipay/pingan',
|
||
`month` char(7) NOT NULL DEFAULT '' COMMENT '月份 YYYY-MM',
|
||
`mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '司机手机号,空=企业全量',
|
||
`invoice_urls` text COMMENT '发票地址(JSON数组)',
|
||
`invoice_count` int(11) NOT NULL DEFAULT 0 COMMENT '发票数量',
|
||
`order_count` int(11) NOT NULL DEFAULT 0 COMMENT '该维度加油明细条数',
|
||
`raw` mediumtext COMMENT '7.14 原始返回(已脱敏)',
|
||
`sync_status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '同步状态 0未同步 1成功 2失败',
|
||
`sync_time` int(11) NOT NULL DEFAULT 0 COMMENT '最近同步时间',
|
||
`sync_text` varchar(255) NOT NULL DEFAULT '' COMMENT '最近同步说明',
|
||
`store_id` int(11) NOT NULL DEFAULT 0 COMMENT '商城ID',
|
||
`create_time` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
|
||
`update_time` int(11) NOT NULL DEFAULT 0 COMMENT '更新时间',
|
||
PRIMARY KEY (`invoice_id`),
|
||
UNIQUE KEY `uk_invoice` (`qyt_enterprise_id`, `passageway`, `month`, `mobile`),
|
||
KEY `idx_month` (`month`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='企油通企业发票(7.14)';
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 4. 司机档案
|
||
-- 按身份证号唯一。累计发放/退款/订单数不存冗余字段，查询时从订单实时汇总，避免对不上账。
|
||
-- conflict 用来提前发现会导致上游二要素核验失败的数据：
|
||
-- 同一身份证号出现不同姓名 / 同一手机号对应多个身份证号 / 手机号变更
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE `yoshop_qyt_driver` (
|
||
`driver_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`id_card` varchar(32) NOT NULL DEFAULT '' COMMENT '身份证号',
|
||
`name` varchar(50) NOT NULL DEFAULT '' COMMENT '姓名(最近一次下单)',
|
||
`mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '手机号(最近一次下单)',
|
||
`conflict` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否存在信息冲突',
|
||
`conflict_text` varchar(255) NOT NULL DEFAULT '' COMMENT '冲突说明',
|
||
`last_order_time` int(11) NOT NULL DEFAULT 0 COMMENT '最近下单时间',
|
||
`remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
|
||
`store_id` int(11) NOT NULL DEFAULT 0 COMMENT '商城ID',
|
||
`create_time` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
|
||
`update_time` int(11) NOT NULL DEFAULT 0 COMMENT '更新时间',
|
||
PRIMARY KEY (`driver_id`),
|
||
UNIQUE KEY `uk_id_card` (`store_id`, `id_card`),
|
||
KEY `idx_mobile` (`mobile`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='企油通司机档案';
|
||
|
||
-- 4.1 用存量订单回填司机档案（姓名/手机号取最近一单）
|
||
INSERT IGNORE INTO `yoshop_qyt_driver` (`id_card`, `name`, `mobile`, `conflict`, `conflict_text`, `last_order_time`, `store_id`, `create_time`, `update_time`)
|
||
SELECT
|
||
UPPER(o.`id_card`),
|
||
SUBSTRING_INDEX(GROUP_CONCAT(o.`driver_name` ORDER BY o.`create_time` DESC SEPARATOR '\n'), '\n', 1),
|
||
SUBSTRING_INDEX(GROUP_CONCAT(o.`mobile` ORDER BY o.`create_time` DESC SEPARATOR '\n'), '\n', 1),
|
||
IF(COUNT(DISTINCT o.`driver_name`) > 1, 1, 0),
|
||
IF(COUNT(DISTINCT o.`driver_name`) > 1, '同一身份证号出现不同姓名（存量订单回填）', ''),
|
||
MAX(o.`create_time`),
|
||
o.`store_id`,
|
||
UNIX_TIMESTAMP(),
|
||
UNIX_TIMESTAMP()
|
||
FROM `yoshop_partners_order` o
|
||
WHERE o.`id_card` <> ''
|
||
GROUP BY o.`store_id`, UPPER(o.`id_card`);
|
||
|
||
-- 4.2 标记「同一手机号对应多个身份证号」
|
||
UPDATE `yoshop_qyt_driver` d
|
||
JOIN (
|
||
SELECT `store_id`, `mobile` FROM `yoshop_qyt_driver`
|
||
WHERE `mobile` <> '' GROUP BY `store_id`, `mobile` HAVING COUNT(*) > 1
|
||
) m ON m.`store_id` = d.`store_id` AND m.`mobile` = d.`mobile`
|
||
SET d.`conflict` = 1,
|
||
d.`conflict_text` = IF(d.`conflict_text` = '', '同一手机号对应多个身份证号', CONCAT(d.`conflict_text`, '；同一手机号对应多个身份证号'));
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 5. 后台菜单（挂在「合作商管理」下；幂等，可重复执行）
|
||
-- 注意：部分环境的菜单表里根本没有「合作商管理」(/partners) 这条父级
|
||
-- （超管拥有全部路由，不依赖菜单表，所以之前一直没暴露），先补父级再挂子菜单，
|
||
-- 否则子查询取不到 menu_id，会报 #1048 Column 'parent_id' cannot be null。
|
||
-- ------------------------------------------------------------
|
||
INSERT INTO `yoshop_store_menu` (`module`, `name`, `path`, `action_mark`, `parent_id`, `sort`, `create_time`, `update_time`)
|
||
SELECT 10, '合作商管理', '/partners', '', 0, 100, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT 1 FROM `yoshop_store_menu` WHERE `path` = '/partners' LIMIT 1) x);
|
||
|
||
INSERT INTO `yoshop_store_menu` (`module`, `name`, `path`, `action_mark`, `parent_id`, `sort`, `create_time`, `update_time`)
|
||
SELECT 10, '司机管理', '/qyt/driver/index', '',
|
||
(SELECT `menu_id` FROM (SELECT `menu_id` FROM `yoshop_store_menu` WHERE `path` = '/partners' LIMIT 1) t),
|
||
100, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT 1 FROM `yoshop_store_menu` WHERE `path` = '/qyt/driver/index' LIMIT 1) x);
|
||
|
||
INSERT INTO `yoshop_store_menu` (`module`, `name`, `path`, `action_mark`, `parent_id`, `sort`, `create_time`, `update_time`)
|
||
SELECT 10, '发票管理', '/qyt/invoice/index', '',
|
||
(SELECT `menu_id` FROM (SELECT `menu_id` FROM `yoshop_store_menu` WHERE `path` = '/partners' LIMIT 1) t),
|
||
100, UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT 1 FROM `yoshop_store_menu` WHERE `path` = '/qyt/invoice/index' LIMIT 1) x);
|
||
|
||
-- ============================================================
|
||
-- 回滚：
|
||
-- ALTER TABLE `yoshop_partners_order`
|
||
-- DROP COLUMN `refund_status`, DROP COLUMN `refund_amount`, DROP COLUMN `refund_time`, DROP COLUMN `refund_text`,
|
||
-- DROP INDEX `idx_refund_status`, DROP INDEX `idx_id_card`;
|
||
-- DROP TABLE `yoshop_qyt_refund`;
|
||
-- DROP TABLE `yoshop_qyt_invoice`;
|
||
-- DROP TABLE `yoshop_qyt_driver`;
|
||
-- DELETE FROM `yoshop_store_menu` WHERE `path` IN ('/qyt/driver/index', '/qyt/invoice/index');
|
||
-- ============================================================
|