-- ============================================================================= -- 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;