-- VIN码充电订单独立存储增量脚本
|
-- MyCat 环境请分逻辑库执行：订单表在 charging_pile_order，菜单在 charging_pile_other。
|
|
USE charging_pile_order;
|
|
CREATE TABLE IF NOT EXISTS `t_vin_charging_order` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '订单编号',
|
`vin_id` bigint NOT NULL COMMENT 'VIN管理记录ID（分片键）',
|
`order_type` int DEFAULT '2' COMMENT '订单类型（VIN码充电订单）',
|
`order_classification` int DEFAULT NULL COMMENT '订单分类（1=线上，2=线下）',
|
`app_user_id` bigint DEFAULT NULL COMMENT '用户id',
|
`app_user_car_id` bigint DEFAULT NULL COMMENT '充电车辆id',
|
`plate_num` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '车牌号',
|
`reservation_time` datetime DEFAULT NULL COMMENT '预约充电时间',
|
`expected_vin` varchar(64) DEFAULT NULL COMMENT '支付车辆VIN快照',
|
`actual_vin` varchar(64) DEFAULT NULL COMMENT '启动时设备识别VIN',
|
`site_id` int DEFAULT NULL COMMENT '站点id',
|
`parking_lot_id` int DEFAULT NULL COMMENT '停车场id',
|
`charging_pile_id` int DEFAULT NULL COMMENT '充电桩id',
|
`charging_gun_id` int DEFAULT NULL COMMENT '充电枪id',
|
`charging_capacity` decimal(10,5) DEFAULT '0.00000' COMMENT '充电总度数',
|
`charging_power` decimal(10,4) DEFAULT NULL COMMENT '充电功率',
|
`start_time` datetime DEFAULT NULL COMMENT '充电开始时间',
|
`end_time` datetime DEFAULT NULL COMMENT '充电结束时间',
|
`status` int DEFAULT NULL COMMENT '订单状态',
|
`end_mode` int DEFAULT NULL COMMENT '结束方式',
|
`recharge_amount` decimal(10,4) DEFAULT '0.0000' COMMENT '充值金额',
|
`recharge_payment_type` int DEFAULT NULL COMMENT '充值支付方式',
|
`recharge_payment_status` int DEFAULT NULL COMMENT '充值支付状态',
|
`pay_method` int DEFAULT NULL COMMENT '充电支付模式',
|
`balance_pay_amount` decimal(10,2) DEFAULT '0.00' COMMENT '余额支付金额',
|
`third_pay_amount` decimal(10,2) DEFAULT '0.00' COMMENT '第三方支付金额',
|
`balance_refund_amount` decimal(10,2) DEFAULT '0.00' COMMENT '余额退款金额',
|
`third_refund_amount` decimal(10,2) DEFAULT '0.00' COMMENT '第三方退款金额',
|
`recharge_serial_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '充值支付第三方流水号',
|
`charge_amount` decimal(10,2) DEFAULT NULL COMMENT '充电金额',
|
`residual_amount` decimal(10,2) DEFAULT NULL COMMENT '充值剩余金额',
|
`order_amount` decimal(10,4) DEFAULT NULL COMMENT '订单金额',
|
`payment_amount` decimal(10,4) DEFAULT NULL COMMENT '支付金额',
|
`refund_amount` decimal(10,4) DEFAULT '0.0000' COMMENT '退款金额',
|
`app_coupon_id` bigint DEFAULT NULL COMMENT '优惠券id',
|
`coupon_discount_amount` decimal(10,4) DEFAULT NULL COMMENT '优惠金额',
|
`is_whitelist` tinyint NOT NULL DEFAULT '0' COMMENT '是否白名单订单',
|
`whitelist_id` bigint DEFAULT NULL COMMENT '白名单用户ID',
|
`whitelist_benefit_type` tinyint DEFAULT NULL COMMENT '白名单权益',
|
`whitelist_discount_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '白名单减免金额',
|
`whitelist_electric_refund_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '白名单电费退款金额',
|
`whitelist_service_refund_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '白名单服务费退款金额',
|
`vip_discount` decimal(10,2) DEFAULT NULL COMMENT '会员折扣',
|
`vip_discount_amount` decimal(10,4) DEFAULT NULL COMMENT '会员优惠金额',
|
`refund_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '退款流水号',
|
`refund_status` int DEFAULT NULL COMMENT '退款状态',
|
`refund_serial_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '退款第三方流水号',
|
`refund_time` datetime DEFAULT NULL COMMENT '退款成功时间',
|
`create_time` datetime DEFAULT NULL COMMENT '添加时间',
|
`del_flag` tinyint(1) DEFAULT '0' COMMENT '删除标记',
|
`order_source` int DEFAULT '1' COMMENT '订单来源',
|
`source_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '平台名称',
|
`operator_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '运营商ID',
|
`tripartite_platform_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '三方平台名称',
|
`start_charge_seq` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '三方平台充电订单号',
|
`commission_amount` decimal(10,4) DEFAULT '0.0000' COMMENT '手续费',
|
`sharing_amount` decimal(10,4) DEFAULT '0.0000' COMMENT '分佣',
|
`service_charge` decimal(10,4) DEFAULT NULL COMMENT '累计服务费',
|
`electrovalence` decimal(10,4) DEFAULT NULL COMMENT '累计电费',
|
`current` decimal(10,2) DEFAULT NULL COMMENT '电流',
|
`voltage` decimal(10,2) DEFAULT NULL COMMENT '电压',
|
`electricity` decimal(10,2) DEFAULT NULL COMMENT '电量',
|
`power` decimal(10,2) DEFAULT NULL COMMENT '功率',
|
`pay_time` datetime DEFAULT NULL COMMENT '最后支付时间',
|
`phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '电话',
|
`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '订单标题',
|
`need_elec` decimal(10,2) DEFAULT NULL COMMENT '实时需求电流',
|
`total_electricity` double DEFAULT NULL COMMENT '剩余电量',
|
`start_soc` decimal(10,2) DEFAULT '0.00' COMMENT '开始SOC',
|
`end_soc` decimal(10,2) DEFAULT NULL COMMENT '结束SOC',
|
PRIMARY KEY USING BTREE (`id`),
|
KEY `idx_vin_id` USING BTREE (`vin_id`),
|
KEY `idx_code` USING BTREE (`code`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='VIN码充电订单'
|
TBPARTITION BY MOD_HASH(vin_id) TBPARTITIONS 20;
|
|
CREATE TABLE IF NOT EXISTS `t_vin_accounting_strategy_detail_order` (
|
`id` int NOT NULL COMMENT '计费策略明细主键id',
|
`accounting_strategy_id` int DEFAULT NULL COMMENT '策略id',
|
`type` int DEFAULT NULL COMMENT '阶段',
|
`start_time` varchar(100) DEFAULT NULL COMMENT '开始时间',
|
`end_time` varchar(100) DEFAULT NULL COMMENT '结束时间',
|
`electrovalence` double(10,4) DEFAULT NULL COMMENT '电价',
|
`service_charge` double(10,4) DEFAULT NULL COMMENT '服务费',
|
`cost_service_charge` double(10,4) DEFAULT NULL COMMENT '原价服务费',
|
`charging_order_id` bigint NOT NULL COMMENT 'VIN码充电订单id（分片键）',
|
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
|
`del_flag` int DEFAULT '0'
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='VIN码订单计费策略明细快照'
|
TBPARTITION BY MOD_HASH(charging_order_id) TBPARTITIONS 20;
|
|
CREATE TABLE IF NOT EXISTS `t_vin_accounting_strategy_order` (
|
`id` int NOT NULL COMMENT '策略id',
|
`site_id` int DEFAULT NULL COMMENT '站点id',
|
`name` varchar(255) DEFAULT NULL COMMENT '策略名称',
|
`description` varchar(255) DEFAULT NULL COMMENT '策略说明',
|
`discount` double(10,2) DEFAULT NULL COMMENT '会员折扣',
|
`create_time` datetime DEFAULT NULL COMMENT '添加时间',
|
`del_flag` int DEFAULT '0' COMMENT '删除状态',
|
`first_user_id` bigint DEFAULT NULL COMMENT '一级审核用户id',
|
`two_user_id` bigint DEFAULT NULL COMMENT '二级审核用户id',
|
`first_remark` varchar(255) DEFAULT NULL COMMENT '一级审核备注',
|
`two_remark` varchar(255) DEFAULT NULL COMMENT '二级审核备注',
|
`audit_status` int DEFAULT '1' COMMENT '审核状态',
|
`first_audit_time` datetime DEFAULT NULL COMMENT '一级审核时间',
|
`two_audit_time` datetime DEFAULT NULL COMMENT '二级审核时间',
|
`user_id` bigint DEFAULT NULL COMMENT '申请人ID',
|
`parent_id` int DEFAULT NULL COMMENT '父级id',
|
`charging_order_id` bigint NOT NULL COMMENT 'VIN码充电订单id（分片键）'
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='VIN码订单计费策略快照'
|
TBPARTITION BY MOD_HASH(charging_order_id) TBPARTITIONS 20;
|
|
CREATE TABLE IF NOT EXISTS `t_vin_charging_order_accounting_strategy` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`charging_order_id` bigint NOT NULL COMMENT 'VIN码充电订单id（分片键）',
|
`accounting_strategy_detail_id` bigint DEFAULT NULL COMMENT '计费策略明细id',
|
`type` int DEFAULT NULL COMMENT '阶段',
|
`start_time` varchar(100) DEFAULT NULL COMMENT '开始时间',
|
`end_time` varchar(100) DEFAULT NULL COMMENT '结束时间',
|
`electrovalence` double(10,4) DEFAULT NULL COMMENT '电价',
|
`service_charge` double(10,4) DEFAULT NULL COMMENT '服务费',
|
`cost_service_charge` double(10,4) DEFAULT NULL COMMENT '原价服务费',
|
`charging_capacity` decimal(10,4) DEFAULT NULL COMMENT '充电度数',
|
`create_time` datetime DEFAULT NULL COMMENT '首次添加时间',
|
`period_electric_price` decimal(10,4) DEFAULT NULL COMMENT '时段电费',
|
`period_service_price` decimal(10,4) DEFAULT NULL COMMENT '时段服务费',
|
`period_original_service_price` decimal(10,4) DEFAULT NULL COMMENT '时段原服务费',
|
`vip_discount_amount` decimal(10,4) DEFAULT NULL COMMENT '会员折扣金额',
|
`coupon_discount_amount` decimal(10,4) DEFAULT NULL COMMENT '优惠券抵扣金额',
|
PRIMARY KEY USING BTREE (`id`),
|
KEY `idx_vin_order_id` USING BTREE (`charging_order_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='VIN码充电订单计费明细'
|
TBPARTITION BY MOD_HASH(charging_order_id) TBPARTITIONS 20;
|
|
CREATE TABLE IF NOT EXISTS `t_vin_charging_order_refund` (
|
`id` bigint NOT NULL COMMENT '主键',
|
`charging_order_id` bigint NOT NULL COMMENT 'VIN码充电订单id（分片键）',
|
`refund_code` varchar(255) DEFAULT NULL COMMENT '退款流水号',
|
`refund_amount` decimal(10,2) DEFAULT NULL COMMENT '退款金额',
|
`balance_refund_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '余额退款金额',
|
`third_refund_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '第三方退款金额',
|
`refund_status` int DEFAULT NULL COMMENT '退款状态',
|
`refund_serial_number` varchar(255) DEFAULT NULL COMMENT '退款第三方流水号',
|
`refund_time` datetime DEFAULT NULL COMMENT '退款成功时间',
|
`code` varchar(255) DEFAULT NULL COMMENT '订单编号',
|
`refund_title` varchar(255) DEFAULT NULL COMMENT '退款标题',
|
`refund_content` varchar(255) DEFAULT NULL COMMENT '退款内容',
|
`refund_reason` varchar(255) DEFAULT NULL COMMENT '退款原因',
|
`refund_remark` varchar(255) DEFAULT NULL COMMENT '退款备注',
|
`refund_total_amount` decimal(10,2) DEFAULT NULL COMMENT '累计退款金额',
|
`refund_fee` decimal(10,2) DEFAULT '0.00' COMMENT '手续费',
|
`pay_amount` decimal(10,2) DEFAULT NULL COMMENT '支付金额',
|
`pay_type` int DEFAULT NULL COMMENT '支付类型',
|
`create_time` datetime DEFAULT NULL,
|
`del_flag` int DEFAULT '0'
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='VIN码充电退款明细'
|
TBPARTITION BY MOD_HASH(charging_order_id) TBPARTITIONS 20;
|
|
CREATE TABLE IF NOT EXISTS `t_vin_charging_order_share_record` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
|
`charging_order_id` bigint DEFAULT NULL COMMENT 'VIN码充电订单ID',
|
`charging_order_code` varchar(64) NOT NULL COMMENT 'VIN码充电订单号',
|
`site_id` int DEFAULT NULL COMMENT '站点ID',
|
`partner_id` int DEFAULT NULL COMMENT '合作商ID',
|
`pay_channel` varchar(16) NOT NULL COMMENT '支付渠道',
|
`channel_pay_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
|
`channel_gift_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
|
`channel_pay_ratio` decimal(10,6) NOT NULL DEFAULT '0.000000',
|
`channel_electricity_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
|
`channel_service_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
|
`electricity_share_ratio` decimal(5,2) DEFAULT NULL,
|
`service_share_ratio` decimal(5,2) DEFAULT NULL,
|
`electricity_share_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
|
`service_share_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
|
`share_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
|
`need_online_share` tinyint(1) NOT NULL DEFAULT '0',
|
`online_share_status` varchar(16) NOT NULL DEFAULT 'RECORDED',
|
`seq_no` varchar(50) DEFAULT NULL,
|
`busi_type` varchar(1) DEFAULT NULL,
|
`ori_mer_id` varchar(20) DEFAULT NULL,
|
`ori_trx_date` date DEFAULT NULL,
|
`sub_order_id` varchar(50) DEFAULT NULL,
|
`sub_mer_id` varchar(20) DEFAULT NULL,
|
`sub_mer_prtcl_no` varchar(20) DEFAULT NULL,
|
`mer_acct` varchar(34) DEFAULT NULL,
|
`icbc_order_id` varchar(64) DEFAULT NULL,
|
`sub_order_seq` varchar(32) DEFAULT NULL,
|
`sub_order_trxid` varchar(64) DEFAULT NULL,
|
`rec_result` varchar(8) DEFAULT NULL,
|
`request_json` text,
|
`response_json` text,
|
`fail_reason` varchar(512) DEFAULT NULL,
|
`retry_count` int NOT NULL DEFAULT '0',
|
`last_retry_time` datetime DEFAULT NULL,
|
`create_time` datetime DEFAULT NULL,
|
`del_flag` int DEFAULT '0',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_order_channel` (`charging_order_code`,`pay_channel`),
|
KEY `idx_share_status` (`online_share_status`),
|
KEY `idx_share_sub_order_id` (`sub_order_id`),
|
KEY `idx_share_partner` (`partner_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='VIN码充电订单全渠道分账记录';
|
|
USE charging_pile_other;
|
|
INSERT INTO `sys_menu`
|
(`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`)
|
SELECT 'VIN码充电订单', parent.menu_id, 2, 'vinChargingOrder', 'order/vinChargingOrder/index', '', 1, 0, 'C', '0', '0', '/vinChargingOrder/list', 'transaction', 'admin', NOW(), '', NULL, 'VIN码充电订单独立查询'
|
FROM `sys_menu` parent
|
WHERE parent.perms = '/vinManagement'
|
AND NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `perms` = '/vinChargingOrder/list');
|
|
INSERT INTO `sys_menu`
|
(`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`)
|
SELECT 'VIN订单详情', parent.menu_id, 1, '#', '', '', 1, 0, 'F', '0', '0', '/vinChargingOrder/detail', '#', 'admin', NOW(), '', NULL, ''
|
FROM `sys_menu` parent
|
WHERE parent.perms = '/vinChargingOrder/list'
|
AND NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `perms` = '/vinChargingOrder/detail');
|
|
INSERT INTO `sys_menu`
|
(`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`update_by`,`update_time`,`remark`)
|
SELECT 'VIN订单导出', parent.menu_id, 2, '#', '', '', 1, 0, 'F', '0', '0', '/vinChargingOrder/export', '#', 'admin', NOW(), '', NULL, ''
|
FROM `sys_menu` parent
|
WHERE parent.perms = '/vinChargingOrder/list'
|
AND NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `perms` = '/vinChargingOrder/export');
|
|
INSERT INTO `sys_role_menu` (`role_id`,`menu_id`)
|
SELECT 1, menu.menu_id
|
FROM `sys_menu` menu
|
WHERE menu.perms IN ('/vinChargingOrder/list','/vinChargingOrder/detail','/vinChargingOrder/export')
|
AND NOT EXISTS (
|
SELECT 1 FROM `sys_role_menu` role_menu
|
WHERE role_menu.role_id = 1 AND role_menu.menu_id = menu.menu_id
|
);
|
