1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
| -- =============================================================================
| -- VIN码充电流程测试数据（余额锁定 + 授权停充 + 优惠结算）
| -- 创建日期: 2026-09-29
| -- 目标环境: 测试库 Mycat(192.168.110.111:8066)，逻辑库 other/account/service
| -- 配套脚本: doc/charging-link-test/run-vd-flow.ps1
| -- 幂等: 可重复执行（先按 99xx 段清理再重建），不影响公司1/2、VIN1、授权1、枪59~70、策略254/259
| --
| -- 执行:
| -- mysql --host=192.168.110.111 --port=8066 --user=root --password=123456 \
| -- --default-character-set=utf8mb4 --batch --force -e "source <本文件>"
| --
| -- 场景矩阵（固定ID段 99xx）:
| -- S1 公司9901 共享(50) VIN99001 授权 全天 site19 auto0 下发30 优惠 1=不优惠
| -- S2 公司9902 分配 VIN99002(余额20) 授权 全天 site19 auto0 下发30 优惠 1=不优惠
| -- S3 公司9903 共享(50) VIN99003 授权 指定时段(now-2h~now+3min) auto1 下发30
| -- S4 公司9904 共享(50) VIN99004 授权 全天 site_ids=25（枪在site19）
| -- S5 公司9905 共享(50) VIN99005 授权 指定时段 00:00~00:00（恒不命中）
| -- S5b 公司9911 共享(50) VIN99011 授权 指定时段 time_ranges=NULL（放行分支）
| -- S6a 公司9906 共享(0) VIN99006 授权 全天 下发30
| -- S6b 公司9907 分配 VIN99007(余额0) 授权 全天 下发30
| -- S7 公司9908 共享(50/停用) VIN99008 授权 全天 下发30
| -- S8a 公司9909 共享(50) VIN99009 授权 全天 site19 auto0 下发30 优惠 2=服务费折扣50%
| -- S8b 公司9910 共享(50) VIN99010 授权 全天 site19 auto0 下发30 优惠 3=计费模板9901
| --
| -- 结算断言依赖（计费策略254，6段，按 start_time 排序；第N段读 {type}_chargeN）:
| -- 段1 00:00-08:00 type4谷 0.3300/0.4000 段2 08:00-10:00 type3平 0.6813/0.4000
| -- 段3 10:00-12:00 type2峰 1.0327/0.4000 段4/5/6 依次 平/峰/谷 同价
| -- 上报 valley_charge1=1.0 + flat_charge2=1.0 + peak_charge3=1.0 ->
| -- S1/S2 电费 0.33+0.68+1.03=2.04 服务费 1.20 -> order_amount=3.24
| -- S8a 服务费单价 0.40x50%=0.20x3=0.60 电费 2.04 -> order_amount=2.64
| -- S8b 模板 谷0.20/0.30 平0.50/0.30 峰0.90/0.30 -> order_amount=2.50
| -- =============================================================================
|
| -- ============ 第0节 清理 ============
| USE `charging_pile_order`;
| -- 注：Mycat 不支持带子查询的删除，计费策略快照表(t_vin_charging_order_accounting_strategy)
| -- 由 run-vd-flow.ps1 先按 vin_id 查出订单ID、再用 IN (...) 显式删除。
| DELETE FROM `t_vin_charging_order` WHERE vin_id BETWEEN 99001 AND 99011;
|
| USE `charging_pile_account`;
| DELETE FROM `t_company_allocate_record` WHERE company_id BETWEEN 9901 AND 9911;
| DELETE FROM `t_vin_management` WHERE id BETWEEN 99001 AND 99011;
|
| USE `charging_pile_other`;
| DELETE FROM `t_charge_authorization` WHERE company_id BETWEEN 9901 AND 9911;
| DELETE FROM `t_company_preferential` WHERE company_id BETWEEN 9901 AND 9911;
| DELETE FROM `t_group_company` WHERE id BETWEEN 9901 AND 9911;
|
| USE `charging_pile_service`;
| DELETE FROM `t_billing_template_detail` WHERE template_id = 9901;
| DELETE FROM `t_billing_template` WHERE id = 9901;
|
| -- ============ 第1节 测试公司（account_mode 1=共享 2=分配; status 1=启用 0=停用） ============
| USE `charging_pile_other`;
| INSERT INTO `t_group_company`
| (`id`,`name`,`account_mode`,`available_balance`,`locked_amount`,`status`,`remark`,`del_flag`) VALUES
| (9901,'VD测试-共享无优惠', 1, 50.00, 0.00, 1,'S1', 0),
| (9902,'VD测试-分配无优惠', 2, 0.00, 0.00, 1,'S2', 0),
| (9903,'VD测试-授权到期停充', 1, 50.00, 0.00, 1,'S3', 0),
| (9904,'VD测试-站点超范围', 1, 50.00, 0.00, 1,'S4', 0),
| (9905,'VD测试-时段外', 1, 50.00, 0.00, 1,'S5', 0),
| (9906,'VD测试-共享余额0', 1, 0.00, 0.00, 1,'S6a',0),
| (9907,'VD测试-分配余额0', 2, 0.00, 0.00, 1,'S6b',0),
| (9908,'VD测试-公司停用', 1, 50.00, 0.00, 0,'S7', 0),
| (9909,'VD测试-比例优惠', 1, 50.00, 0.00, 1,'S8a',0),
| (9910,'VD测试-模板优惠', 1, 50.00, 0.00, 1,'S8b',0),
| (9911,'VD测试-空时段放行', 1, 50.00, 0.00, 1,'S5b',0);
|
| -- ============ 第2节 测试VIN（vin 为17位纯数字，倒序即设备上报 account） ============
| USE `charging_pile_account`;
| INSERT INTO `t_vin_management`
| (`id`,`vin`,`plate_number`,`company_name`,`company_id`,`balance`,`locked_amount`,`remark`,`status`,`del_flag`) VALUES
| (99001,'99000000000000001','川T99001','VD测试-共享无优惠', 9901, 0.00,0.00,'S1', 1,0),
| (99002,'99000000000000002','川T99002','VD测试-分配无优惠', 9902, 20.00,0.00,'S2', 1,0),
| (99003,'99000000000000003','川T99003','VD测试-授权到期停充', 9903, 0.00,0.00,'S3', 1,0),
| (99004,'99000000000000004','川T99004','VD测试-站点超范围', 9904, 0.00,0.00,'S4', 1,0),
| (99005,'99000000000000005','川T99005','VD测试-时段外', 9905, 0.00,0.00,'S5', 1,0),
| (99006,'99000000000000006','川T99006','VD测试-共享余额0', 9906, 0.00,0.00,'S6a',1,0),
| (99007,'99000000000000007','川T99007','VD测试-分配余额0', 9907, 0.00,0.00,'S6b',1,0),
| (99008,'99000000000000008','川T99008','VD测试-公司停用', 9908, 0.00,0.00,'S7', 1,0),
| (99009,'99000000000000009','川T99009','VD测试-比例优惠', 9909, 0.00,0.00,'S8a',1,0),
| (99010,'99000000000000010','川T99010','VD测试-模板优惠', 9910, 0.00,0.00,'S8b',1,0),
| (99011,'99000000000000011','川T99011','VD测试-空时段放行', 9911, 0.00,0.00,'S5b',1,0);
|
| -- ============ 第3节 充电授权（time_mode 1=全天 2=指定时段 3=指定电价时段; auto_charge 1=到期自动停充） ============
| USE `charging_pile_other`;
| INSERT INTO `t_charge_authorization`
| (`id`,`company_id`,`time_mode`,`time_ranges`,`price_types`,`auto_charge`,`site_ids`,`dispatch_amount`,`del_flag`) VALUES
| (9901,9901,1,NULL, NULL, 0,'19',30.00,0),
| (9902,9902,1,NULL, NULL, 0,'19',30.00,0),
| -- S3: 当前时间-2h ~ 当前时间+3min（跨零点窗口退化为 00:00~23:59，由脚本推进 max_end_time 验证停充）
| (9903,9903,2,
| CASE WHEN HOUR(NOW()) < 3 OR HOUR(NOW()) >= 23
| THEN '[{"startTime":"00:00","endTime":"23:59"}]'
| ELSE CONCAT('[{"startTime":"', DATE_FORMAT(NOW() - INTERVAL 2 HOUR, '%H:%i'),
| '","endTime":"', DATE_FORMAT(NOW() + INTERVAL 3 MINUTE, '%H:%i'), '"}]')
| END,
| NULL,1,'19',30.00,0),
| (9904,9904,1,NULL, NULL, 0,'25',30.00,0),
| (9905,9905,2,'[{"startTime":"00:00","endTime":"00:00"}]', NULL,0,'19',30.00,0),
| (9906,9906,1,NULL, NULL, 0,'19',30.00,0),
| (9907,9907,1,NULL, NULL, 0,'19',30.00,0),
| (9908,9908,1,NULL, NULL, 0,'19',30.00,0),
| (9909,9909,1,NULL, NULL, 0,'19',30.00,0),
| (9910,9910,1,NULL, NULL, 0,'19',30.00,0),
| -- S5b: 指定时段但未配置时间段 -> 服务端按放行处理
| (9911,9911,2,NULL, NULL, 0,'19',30.00,0);
|
| -- ============ 第4节 公司优惠设置与计费模板 ============
| -- preferential_type: 1=不优惠 2=服务费按比例折扣(service_discount_rate 百分比) 3=按计费模板优惠单价
| USE `charging_pile_other`;
| INSERT INTO `t_company_preferential`
| (`id`,`company_id`,`preferential_type`,`service_discount_rate`,`billing_template_id`,`del_flag`) VALUES
| (9901,9909,2,50.00,NULL,0),
| -- S8b 优惠方式3 必须落在本脚本第4节创建的计费模板 9901 上（曾误写 9910 导致模板查不到、按原价结算）
| (9902,9910,3,NULL,9901,0);
|
| USE `charging_pile_service`;
| INSERT INTO `t_billing_template` (`id`,`name`,`description`,`discount`,`del_flag`) VALUES
| (9901,'VD测试-模板优惠','VIN码充电流程测试 S8b',NULL,0);
| INSERT INTO `t_billing_template_detail`
| (`id`,`template_id`,`type`,`start_time`,`end_time`,`electrovalence`,`service_charge`,`cost_service_charge`,`del_flag`) VALUES
| (9901,9901,4,'00:00','08:00',0.2000,0.3000,0.3000,0),
| (9902,9901,3,'08:00','10:00',0.5000,0.3000,0.3000,0),
| (9903,9901,2,'10:00','12:00',0.9000,0.3000,0.3000,0);
|
| -- ============ 第5节 回查（执行后应输出 11 家公司 / 11 个VIN / 11 条授权 / 2 条优惠 / 模板3明细） ============
| SELECT 'COMPANY' AS tag, id, name, account_mode, available_balance, locked_amount, status
| FROM charging_pile_other.t_group_company WHERE id BETWEEN 9901 AND 9911 ORDER BY id;
| SELECT 'VIN' AS tag, id, vin, company_id, balance, locked_amount, status
| FROM charging_pile_account.t_vin_management WHERE id BETWEEN 99001 AND 99011 ORDER BY id;
| SELECT 'AUTH' AS tag, id, company_id, time_mode, time_ranges, auto_charge, site_ids, dispatch_amount
| FROM charging_pile_other.t_charge_authorization WHERE id BETWEEN 9901 AND 9911 ORDER BY id;
| SELECT 'PREF' AS tag, id, company_id, preferential_type, service_discount_rate, billing_template_id
| FROM charging_pile_other.t_company_preferential ORDER BY id;
| SELECT 'TPL' AS tag, id, template_id, type, start_time, end_time, electrovalence, service_charge
| FROM charging_pile_service.t_billing_template_detail WHERE template_id = 9901 ORDER BY id;
| SELECT 'GUN' AS tag, g.id, g.code, g.status, g.site_id, p.code AS pile_code
| FROM charging_pile_service.t_charging_gun g
| JOIN charging_pile_service.t_charging_pile p ON p.id = g.charging_pile_id
| WHERE g.del_flag = 0 AND p.del_flag = 0 AND g.site_id = 19 ORDER BY g.id;
|
|
