110 lines
6.8 KiB
SQL
110 lines
6.8 KiB
SQL
-- =============================================================================
|
||
-- IT-2 / DV-2 Mock 灌数（可重放）：3 交路 / 5 房间 / 5 人员 / 2 天计划。
|
||
-- 依据：公寓端/09 表 9-16（Mock source='LOCAL'、source_version=0）、表 13-12、表 7-7 步骤 2。
|
||
-- 所有数据标 LOCAL / version=0，页面须显示「非平台下发」；不得冒充真实平台下行。
|
||
-- =============================================================================
|
||
SET @apt_gyid = 'APT-REV01-001';
|
||
|
||
-- 1. 主数据版本快照：6 个域均 APPLIED / LOCAL / source_version=0
|
||
INSERT INTO apt_master_version (gyid, domain, source_version, source, state, applied_at, remark, deleted)
|
||
VALUES
|
||
(@apt_gyid, 'APARTMENT', 0, 'LOCAL', 'APPLIED', NOW(3), 'IT-2 Mock', 0),
|
||
(@apt_gyid, 'ROOM', 0, 'LOCAL', 'APPLIED', NOW(3), 'IT-2 Mock', 0),
|
||
(@apt_gyid, 'PERSON', 0, 'LOCAL', 'APPLIED', NOW(3), 'IT-2 Mock', 0),
|
||
(@apt_gyid, 'ROUTE', 0, 'LOCAL', 'APPLIED', NOW(3), 'IT-2 Mock', 0),
|
||
(@apt_gyid, 'RULE', 0, 'LOCAL', 'APPLIED', NOW(3), 'IT-2 Mock', 0),
|
||
(@apt_gyid, 'HOLIDAY', 0, 'LOCAL', 'APPLIED', NOW(3), 'IT-2 Mock', 0)
|
||
ON DUPLICATE KEY UPDATE source = VALUES(source), state = VALUES(state), remark = VALUES(remark);
|
||
|
||
-- 2. 3 条交路（jlid 唯一；kcsj 为开车时刻，jcsj 为基线，权威值由复算服务给出）
|
||
INSERT INTO apt_master_route
|
||
(gyid, jlid, cc, kcsj, jcsj, lysj, route_name, start_station, end_station,
|
||
cclx, bz, state, stop_reason, source_version, deleted)
|
||
VALUES
|
||
(@apt_gyid, 'JL-001', 'G101', DATE_ADD(CURDATE(), INTERVAL 8 HOUR),
|
||
DATE_ADD(CURDATE(), INTERVAL 6 HOUR), DATE_ADD(CURDATE(), INTERVAL 5 HOUR),
|
||
'北京南-济南西', '北京南', '济南西', 'HIGH_SPEED', 'IT-2 Mock', 'ACTIVE', NULL, 0, 0),
|
||
(@apt_gyid, 'JL-002', 'G203', DATE_ADD(CURDATE(), INTERVAL 12 HOUR),
|
||
DATE_ADD(CURDATE(), INTERVAL 10 HOUR), DATE_ADD(CURDATE(), INTERVAL 9 HOUR),
|
||
'济南西-上海虹桥', '济南西', '上海虹桥', 'HIGH_SPEED', 'IT-2 Mock', 'ACTIVE', NULL, 0, 0),
|
||
(@apt_gyid, 'JL-003', 'K51', DATE_ADD(CURDATE(), INTERVAL 18 HOUR),
|
||
DATE_ADD(CURDATE(), INTERVAL 16 HOUR), DATE_ADD(CURDATE(), INTERVAL 15 HOUR),
|
||
'北京-青岛北', '北京', '青岛北', 'NORMAL_SPEED', 'IT-2 Mock', 'STOPPED', 'Mock 停用样例', 0, 0)
|
||
ON DUPLICATE KEY UPDATE cc = VALUES(cc), bz = VALUES(bz),
|
||
route_name = VALUES(route_name), start_station = VALUES(start_station),
|
||
end_station = VALUES(end_station), stop_reason = VALUES(stop_reason);
|
||
|
||
-- 3. 5 个房间：平台基线 + 运行时房态各一条
|
||
INSERT INTO apt_master_room
|
||
(gyid, room_id, room_no, room_floor, bed_count, orientation, room_type, state, source_version, deleted)
|
||
VALUES
|
||
(@apt_gyid, 'ROOM-201', '201', '2', 2, 'SOUTH', 'DOUBLE', 'ACTIVE', 0, 0),
|
||
(@apt_gyid, 'ROOM-202', '202', '2', 2, 'NORTH', 'DOUBLE', 'ACTIVE', 0, 0),
|
||
(@apt_gyid, 'ROOM-301', '301', '3', 3, 'SOUTH', 'TRIPLE', 'ACTIVE', 0, 0),
|
||
(@apt_gyid, 'ROOM-302', '302', '3', 2, 'EAST', 'DOUBLE', 'ACTIVE', 0, 0),
|
||
(@apt_gyid, 'ROOM-303', '303', '3', 1, 'WEST', 'SINGLE', 'ACTIVE', 0, 0)
|
||
ON DUPLICATE KEY UPDATE room_no = VALUES(room_no), room_floor = VALUES(room_floor);
|
||
|
||
INSERT INTO gy_rooms
|
||
(gyid, room_id, room_no, room_floor, room_status, in_person_count, state_version,
|
||
room_type, room_order, room_dir, bed_numbers, deleted)
|
||
VALUES
|
||
(@apt_gyid, 'ROOM-201', '201', '2', '1', 0, 1, 'DOUBLE', 1, 'SOUTH', '201-1,201-2', 0),
|
||
(@apt_gyid, 'ROOM-202', '202', '2', '5', 1, 1, 'DOUBLE', 2, 'NORTH', '202-1,202-2', 0),
|
||
(@apt_gyid, 'ROOM-301', '301', '3', '2', 3, 1, 'TRIPLE', 3, 'SOUTH', '301-1,301-2,301-3', 0),
|
||
(@apt_gyid, 'ROOM-302', '302', '3', '3', 0, 1, 'DOUBLE', 4, 'EAST', '302-1,302-2', 0),
|
||
(@apt_gyid, 'ROOM-303', '303', '3', '4', 0, 1, 'SINGLE', 5, 'WEST', '303-1', 0)
|
||
ON DUPLICATE KEY UPDATE room_no = VALUES(room_no), room_floor = VALUES(room_floor);
|
||
|
||
-- 4. 5 名人员（xm 为平台快照字段，本地改须返回 40904）
|
||
INSERT INTO apt_master_person
|
||
(gyid, ryid, xm, work_no, dept_code, person_category, state, source_version, deleted)
|
||
VALUES
|
||
(@apt_gyid, 'RY-001', '张三', 'W001', 'D01', 'TRANSIENT', 'ACTIVE', 0, 0),
|
||
(@apt_gyid, 'RY-002', '李四', 'W002', 'D01', 'TRANSIENT', 'ACTIVE', 0, 0),
|
||
(@apt_gyid, 'RY-003', '王五', 'W003', 'D02', 'LONG_TERM', 'ACTIVE', 0, 0),
|
||
(@apt_gyid, 'RY-004', '赵六', 'W004', 'D02', 'TRANSIENT', 'ACTIVE', 0, 0),
|
||
(@apt_gyid, 'RY-005', '钱七', 'W005', 'D03', 'TRANSIENT', 'ACTIVE', 0, 0)
|
||
ON DUPLICATE KEY UPDATE xm = VALUES(xm), dept_code = VALUES(dept_code);
|
||
|
||
-- 5. 2 天计划（jcsj 仅基线，computed_jcsj 为公寓复算权威值）
|
||
INSERT INTO gy_syn_jcxx
|
||
(gyid, jcid, jlid, cc, ryid, xm, fjh, kcsj, jcsj, computed_jcsj,
|
||
plan_version, sync_state, source_version, deleted)
|
||
VALUES
|
||
(@apt_gyid, 'PLAN-D1-001', 'JL-001', 'G101', 'RY-001', '张三', '201',
|
||
DATE_ADD(CURDATE(), INTERVAL 8 HOUR), DATE_ADD(CURDATE(), INTERVAL 6 HOUR),
|
||
DATE_ADD(CURDATE(), INTERVAL 6 HOUR), 1, 'SYNCED', 0, 0),
|
||
(@apt_gyid, 'PLAN-D2-001', 'JL-002', 'G203', 'RY-002', '李四', '202',
|
||
DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 12 HOUR),
|
||
DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 10 HOUR),
|
||
DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 10 HOUR), 1, 'SYNCED', 0, 0)
|
||
ON DUPLICATE KEY UPDATE xm = VALUES(xm), fjh = VALUES(fjh);
|
||
|
||
SELECT '=== IT-2 Mock 数据量（交路/房间/人员/计划/版本）===' AS section;
|
||
SELECT 'route' AS domain, COUNT(*) AS cnt FROM apt_master_route WHERE gyid = @apt_gyid AND deleted = 0
|
||
UNION ALL SELECT 'room', COUNT(*) FROM gy_rooms WHERE gyid = @apt_gyid AND deleted = 0
|
||
UNION ALL SELECT 'person', COUNT(*) FROM apt_master_person WHERE gyid = @apt_gyid AND deleted = 0
|
||
UNION ALL SELECT 'plan', COUNT(*) FROM gy_syn_jcxx WHERE gyid = @apt_gyid AND deleted = 0
|
||
UNION ALL SELECT 'version', COUNT(*) FROM apt_master_version
|
||
WHERE gyid = @apt_gyid AND state = 'APPLIED' AND source = 'LOCAL';
|
||
|
||
-- 6. 版本标记纪律校验（公寓端/09 表 9-16、08 R-03）
|
||
SELECT '=== 版本标记：LOCAL / source_version=0 / APPLIED（应为 6）===' AS section;
|
||
SELECT COUNT(*) AS local_applied_domains
|
||
FROM apt_master_version
|
||
WHERE gyid = @apt_gyid AND source = 'LOCAL' AND source_version = 0
|
||
AND state = 'APPLIED' AND deleted = 0;
|
||
|
||
SELECT '=== 不得冒充平台下发（应为 0）===' AS section;
|
||
SELECT COUNT(*) AS fake_platform_rows
|
||
FROM apt_master_version
|
||
WHERE gyid = @apt_gyid AND source = 'PLATFORM' AND source_version = 0 AND deleted = 0;
|
||
|
||
SELECT '=== 各域版本明细（页面须显示「非平台下发」）===' AS section;
|
||
SELECT domain, source, source_version AS sourceVersion, state,
|
||
CASE WHEN source = 'LOCAL' THEN '非平台下发' ELSE '平台下发' END AS sourceLabel
|
||
FROM apt_master_version
|
||
WHERE gyid = @apt_gyid AND deleted = 0
|
||
ORDER BY domain;
|