143 lines
10 KiB
SQL
143 lines
10 KiB
SQL
|
-- =====================================================================
|
||
|
|
-- deploy/integration/postgres-init/03-basicdata-seed.sql
|
||
|
|
-- 基础数据测试种子（联调栈，DEV ONLY）：成都双流 T2 语境的人造数据，
|
||
|
|
-- 不是生产数据，仅供 admin-api `/basicdata/*` 与手工查验。
|
||
|
|
--
|
||
|
|
-- 与 deploy/dev/mysql-init/02-mailbox-messages.sql
|
||
|
|
-- 对齐同一套编码：登机门 D06/D08、值机柜台 C05/C06、滑槽 CH04、转盘 4、
|
||
|
|
-- 机号 B-2087/B-8690、机型 320/738/321。
|
||
|
|
-- 季度计划（fims_*）不造数，供数方待定（`Q25`）。
|
||
|
|
-- 执行环境：docker-entrypoint-initdb.d，仅空卷首启时以 msgx_dev 运行。
|
||
|
|
-- =====================================================================
|
||
|
|
|
||
|
|
SET search_path = basicdata;
|
||
|
|
|
||
|
|
INSERT INTO sys_country (COUNTRY_ID, COUNTRY_CODE, COUNTRY_NAME_CHN, COUNTRY_NAME_ENG, OPERATE) VALUES
|
||
|
|
(1, 'CN', '中国', 'China', 1),
|
||
|
|
(2, 'US', '美国', 'United States', 1),
|
||
|
|
(3, 'JP', '日本', 'Japan', 1),
|
||
|
|
(4, 'KR', '韩国', 'Korea', 1),
|
||
|
|
(5, 'SG', '新加坡', 'Singapore', 1),
|
||
|
|
(6, 'TH', '泰国', 'Thailand', 1);
|
||
|
|
|
||
|
|
INSERT INTO sys_city (CITY_ID, CITY_CODE, CITY_ICCDE, CITY_NAME_CHN, CITY_NAME_ENG, COUNTRY_ID, MAIN_CITY_TAG, OPERATE, TEL_CITY_CODE) VALUES
|
||
|
|
(1, 'CTU', 'ZUUU', '成都', 'Chengdu', 1, 1, 1, '028'),
|
||
|
|
(2, 'PEK', 'ZBAA', '北京', 'Beijing', 1, 1, 1, '010'),
|
||
|
|
(3, 'SHA', 'ZSSS', '上海', 'Shanghai', 1, 1, 1, '021'),
|
||
|
|
(4, 'CAN', 'ZGGG', '广州', 'Guangzhou', 1, 1, 1, '020'),
|
||
|
|
(5, 'SZX', 'ZGSZ', '深圳', 'Shenzhen', 1, 1, 1, '0755'),
|
||
|
|
(6, 'SIN', 'WSSS', '新加坡', 'Singapore', 5, 1, 1, NULL);
|
||
|
|
|
||
|
|
INSERT INTO sys_airportgroup (AIRPORT_GROUP_ID, AIRPORT_GROUP_CODE, AIRPORT_GROUP_NAME) VALUES
|
||
|
|
(1, 'CDIA', '成都双流国际机场集团');
|
||
|
|
|
||
|
|
INSERT INTO sys_airport (AIRPORT_ID, AIRPORT_CODE, AIRPORT_CODE_CAA, AIRPORT_CODE_IATA, AIRPORT_CODE_ICAO, AIRPORT_GROUP_ID, AIRPORT_NAME_CHN, AIRPORT_NAME_ENG, BRIEF_NAME_CHN, BRIEF_NAME_CHN_COPY, BRIEF_NAME_ENG, CITY_ID, HAUL, HOST_FLAG, INTERNATIONAL_FLAG, OPERATE) VALUES
|
||
|
|
(1, 'CTU', 'CTU', 'CTU', 'ZUUU', 1, '成都双流国际机场', 'Chengdu Shuangliu International Airport', '双流', '成都', 'Shuangliu', 1, 'L', 1, 1, 1),
|
||
|
|
(2, 'TFU', 'TFU', 'TFU', 'ZUTF', 1, '成都天府国际机场', 'Chengdu Tianfu International Airport', '天府', '成都', 'Tianfu', 1, 'L', 1, 1, 1),
|
||
|
|
(3, 'PEK', 'PEK', 'PEK', 'ZBAA', NULL, '北京首都国际机场', 'Beijing Capital International Airport', '首都', '北京', 'Capital', 2, 'L', 0, 1, 1),
|
||
|
|
(4, 'SZX', 'SZX', 'SZX', 'ZGSZ', NULL, '深圳宝安国际机场', 'Shenzhen Baoan International Airport', '宝安', '深圳', 'Baoan', 5, 'L', 0, 1, 1),
|
||
|
|
(5, 'SIN', 'SIN', 'SIN', 'WSSS', NULL, '新加坡樟宜机场', 'Singapore Changi Airport', '樟宜', '新加坡', 'Changi', 6, 'L', 0, 1, 1);
|
||
|
|
|
||
|
|
INSERT INTO sys_airlinegroup (AIRLINE_GROUP_ID, AIRLINE_GROUP_CODE, AIRLINE_GROUP_NAME, AIRLINE_GROUP_CODE_CAA, AIRLINE_GROUP_CODE_IATA, AIRLINE_GROUP_CODE_ICAO) VALUES
|
||
|
|
(1, 'CA', '中国国际航空集团', 'CA', 'CA', 'CCA'),
|
||
|
|
(2, 'CZ', '中国南方航空集团', 'CZ', 'CZ', 'CSN'),
|
||
|
|
(3, '3U', '四川航空集团', '3U', '3U', 'CSC');
|
||
|
|
|
||
|
|
INSERT INTO sys_airline (AIRLINE_ID, AIRLINE_CODE, AIRLINE_CODE_CAA, AIRLINE_CODE_IATA, AIRLINE_CODE_ICAO, AIRLINE_GROUP_ID, AIRLINE_NAME, AIRLINE_NAME_ENG, BRIEF_NAME_CHN, BRIEF_NAME_ENG, CODE, COUNTRY_ID, HOST_FLAG, INTERNATIONAL_FLAG, NAMC, NAME, OPERATE, PARENT_AIRLINE_ID) VALUES
|
||
|
|
(1, 'CA', 'CA', 'CA', 'CCA', 1, '中国国际航空股份有限公司', 'Air China', '国航', 'Air China', 'CA', 1, 0, 1, NULL, NULL, 1, 1),
|
||
|
|
(2, 'CZ', 'CZ', 'CZ', 'CSN', 2, '中国南方航空股份有限公司', 'China Southern', '南航', 'China Southern', 'CZ', 1, 0, 1, NULL, NULL, 1, 2),
|
||
|
|
(3, '3U', '3U', '3U', 'CSC', 3, '四川航空股份有限公司', 'Sichuan Airlines', '川航', 'Sichuan Airlines', '3U', 1, 1, 1, NULL, NULL, 1, 3),
|
||
|
|
(4, 'MU', 'MU', 'MU', 'CES', NULL, '中国东方航空股份有限公司', 'China Eastern', '东航', 'China Eastern', 'MU', 1, 0, 1, NULL, NULL, 1, NULL);
|
||
|
|
|
||
|
|
INSERT INTO sys_aircrafttypegroup (AIRCRAFTTYPEGROUP_ID, AIRCRAFTTYPEGROUP_CODE, AIRCRAFTTYPEGROUP_NAME) VALUES
|
||
|
|
(1, 'A320', '空客 A320 系列'),
|
||
|
|
(2, 'B737', '波音 737 系列'),
|
||
|
|
(3, 'A350', '空客 A350 系列');
|
||
|
|
|
||
|
|
INSERT INTO sys_aircrafttype (AIRCRAFT_TYPE_CODE, AIRCRAFT_HEIGHT, AIRCRAFT_LENGTH, AIRCRAFT_TYPE_CODE_CAA, AIRCRAFT_TYPE_CODE_IATA, AIRCRAFT_TYPE_CODE_ICAO, AIRCRAFT_TYPE_NAME, AIRCRAFT_WIDTH, AIRCRAFTTYPEGROUP_ID, CHAP, MAX_AIRBRIDGES, MAX_FREIGHT_WEIGHT, MAX_PASSENGERS, MAX_TAKEOFF_WEIGHT, OPERATE, TRANSITSTOPTIME) VALUES
|
||
|
|
('320', 11.76, 37.57, '320', '320', 'A320', '空客 A320', 34.10, 1, 'C', 1, 20000, 158, 78000, 1, 45),
|
||
|
|
('321', 11.76, 44.51, '321', '321', 'A321', '空客 A321', 34.10, 1, 'C', 1, 25000, 199, 93500, 1, 50),
|
||
|
|
('738', 12.50, 39.50, '738', '738', 'B738', '波音 737-800', 35.80, 2, 'C', 1, 20000, 189, 79000, 1, 45),
|
||
|
|
('359', 17.05, 66.80, '359', '359', 'A359', '空客 A350-900', 64.75, 3, 'C', 2, 40000, 312, 280000, 1, 60);
|
||
|
|
|
||
|
|
INSERT INTO sys_aircraft (AIRCRAFT_ID, AIRCRAFT_NUMBER, AIRCRAFT_OWNER_CODE, AIRCRAFT_TYPE_CODE, AIRLINE_CODE, MAX_AIRBRIDGES, MAX_FREIGHT_WEIGHT, MAX_PASSENGERS, MAX_TAKEOFF_WEIGHT, OPERATE, OWID) VALUES
|
||
|
|
(1, 'B-2087', 'CA', '738', 'CA', 1, 20000, 189, 79000, 1, NULL),
|
||
|
|
(2, 'B-8690', '3U', '320', '3U', 1, 20000, 158, 78000, 1, NULL),
|
||
|
|
(3, 'B-8692', '3U', '320', '3U', 1, 20000, 158, 78000, 1, NULL),
|
||
|
|
(4, 'B-32D1', '3U', '321', '3U', 1, 25000, 199, 93500, 1, NULL);
|
||
|
|
|
||
|
|
INSERT INTO sys_flight_type (FLIGHT_TYPE_CODE, FLIGHT_TYPE_CAA_CODE, FLIGHT_TYPE_NAME, C_TAG, VIP_TAG, FLIGHT_TYPE_NAME_CN, OPERATE) VALUES
|
||
|
|
('S', 'S', 'Scheduled', 'N', 'N', '定期航班', 1),
|
||
|
|
('N', 'N', 'Non-scheduled', 'N', 'N', '非定期航班', 1),
|
||
|
|
('C', 'C', 'Charter', 'N', 'N', '包机', 1),
|
||
|
|
('F', 'F', 'Ferry', 'N', 'N', '调机', 1);
|
||
|
|
|
||
|
|
INSERT INTO sys_flight_status (STTC, ABNS, STDC, STTD) VALUES
|
||
|
|
('SCH', 'Scheduled', '计划', 'N'),
|
||
|
|
('EST', 'Estimated', '预计', 'N'),
|
||
|
|
('ACT', 'Actual', '实际', 'N'),
|
||
|
|
('DLY', 'Delayed', '延误', 'Y'),
|
||
|
|
('CNL', 'Cancelled', '取消', 'Y'),
|
||
|
|
('DIV', 'Diverted', '备降', 'Y');
|
||
|
|
|
||
|
|
INSERT INTO sys_flightagent (FLIGHTAGENT_ID, FLIGHTAGENT_NAME, OGID) VALUES
|
||
|
|
('AG01', '成都双流机场地面服务有限公司', 'OG01'),
|
||
|
|
('AG02', '四川机场集团航空服务有限公司', 'OG02');
|
||
|
|
|
||
|
|
INSERT INTO fims_flightstatus_definition (FLIGHTSTATUSDEF_REC_ID, FLIGHT_STATUS, FLIGHT_STATUS_TYPE, FLIGHT_STATUS_DESC, ENABLED_FLAG, DCOD, DCDN, DDES, DDSC, OPERATE) VALUES
|
||
|
|
(1, '气象延误', 'DELY', '天气原因延误', 'Enabled', 'WX', 1, 'Weather delay', '气象延误', 1),
|
||
|
|
(2, '旅客延误', 'DELY', '旅客原因延误', 'Enabled', 'PA', 2, 'Passenger delay', '旅客延误', 1),
|
||
|
|
(3, '流量延误', 'DELY', '流量控制原因延误', 'Enabled', 'FC', 3, 'Flow control delay', '流量延误', 1),
|
||
|
|
(4, '机务延误', 'DELY', '机务原因延误', 'Enabled', 'MT', 4, 'Maintenance delay', '机务延误', 1);
|
||
|
|
|
||
|
|
INSERT INTO orms_terminal (TERMINAL_CODE, OPERATE, TERMINAL_NAME) VALUES
|
||
|
|
('T1', 1, '成都双流 T1 航站楼'),
|
||
|
|
('T2', 1, '成都双流 T2 航站楼');
|
||
|
|
|
||
|
|
INSERT INTO orms_terminalarea (TERMINAL_AREA_CODE, ROUTE_TYPE, TERMINAL_AREA_NAME, TERMINAL_CODE) VALUES
|
||
|
|
('T2C', 'D', 'T2 国内指廊 C', 'T2'),
|
||
|
|
('T2D', 'D', 'T2 国内指廊 D', 'T2'),
|
||
|
|
('T1I', 'I', 'T1 国际指廊', 'T1');
|
||
|
|
|
||
|
|
INSERT INTO orms_standtype (STAND_TYPE, STAND_TYPE_DESC) VALUES
|
||
|
|
('bridge', '靠桥机位'),
|
||
|
|
('remote', '远机位'),
|
||
|
|
('near', '近机位'),
|
||
|
|
('night', '过夜机位'),
|
||
|
|
('specific', '专机位');
|
||
|
|
|
||
|
|
INSERT INTO orms_stand (STAND_CODE, CURRENT_FLIGHT_ID, DDGT, DIGT, FSTAND_CODE, ICON_LENGTH, ICON_WIDTH, LSTAND_CODE, MAX_PERMIT, MAXFLIGHTNUM, OPERATE, PIER_CODE, POS_X, POS_Y, RSTAND_CODE, SATC, STAND_AREA_CODE, STAND_LAYOUTED, STAND_LENGTH, STAND_NAME, STAND_ORDER, STAND_STATUS, STAND_TYPE, STAND_WIDTH, STML, TERMINAL_AREA_CODE) VALUES
|
||
|
|
('101', NULL, 'D06', NULL, NULL, 40, 20, NULL, 264, 1, 1, 'C', 100, 200, NULL, '738', 'T2C', 1, 60, '101 号机位', 1, 0, 'bridge', 45, 'T2', 'T2C'),
|
||
|
|
('102', NULL, 'D06', NULL, NULL, 40, 20, NULL, 264, 1, 1, 'C', 140, 200, NULL, '738', 'T2C', 1, 60, '102 号机位', 2, 0, 'bridge', 45, 'T2', 'T2C'),
|
||
|
|
('105', NULL, 'D08', NULL, NULL, 40, 20, NULL, 199, 1, 1, 'D', 200, 300, NULL, '321', 'T2D', 1, 55, '105 号机位', 3, 0, 'bridge', 45, 'T2', 'T2D'),
|
||
|
|
('201', NULL, NULL, NULL, NULL, 40, 20, NULL, 312, 2, 1, 'C', 300, 400, NULL, '359', 'T2C', 1, 70, '201 号远机位', 4, 0, 'remote', 60, 'T2', 'T2C');
|
||
|
|
|
||
|
|
INSERT INTO orms_stand_airbridge (STAND_CODE, AIRBRIDGE_CODE) VALUES
|
||
|
|
('101', 'D06'),
|
||
|
|
('102', 'D06'),
|
||
|
|
('105', 'D08');
|
||
|
|
|
||
|
|
INSERT INTO orms_gate (GATE_CODE, ACTION_ORDER, GATE_CATEGORY, GATE_NAME, GATE_STATUS, GATE_TYPE, GTML, ICON_LENGTH, ICON_WIDTH, OPERATE, PIER_CODE, POS_X, POS_Y, STAND_LAYOUTED, TERMINAL_AREA_CODE) VALUES
|
||
|
|
('C05', 1, 'D', 'C05 登机口', 0, '2', 'T2', 30, 20, 1, 'C', 110, 210, 1, 'T2C'),
|
||
|
|
('C06', 2, 'D', 'C06 登机口', 0, '2', 'T2', 30, 20, 1, 'C', 150, 210, 1, 'T2C'),
|
||
|
|
('D06', 3, 'D', 'D06 登机口', 0, '2', 'T2', 30, 20, 1, 'D', 210, 310, 1, 'T2D'),
|
||
|
|
('D08', 4, 'D', 'D08 登机口', 0, '2', 'T2', 30, 20, 1, 'D', 250, 310, 1, 'T2D');
|
||
|
|
|
||
|
|
INSERT INTO orms_chut (CHUT_CODE, BCAT, BTML, CHUT_LENGTH, CHUT_NAME, CHUT_STATUS, OPERATE) VALUES
|
||
|
|
('CH04', 'D', 'T2', 12, 'CH04 行李滑槽', 'E', 1),
|
||
|
|
('CH05', 'D', 'T2', 12, 'CH05 行李滑槽', 'E', 1);
|
||
|
|
|
||
|
|
INSERT INTO orms_checkin_group (CHECKIN_GROUP_CODE, CHECKIN_GROUP_NAME) VALUES
|
||
|
|
('A', 'A 值机岛'),
|
||
|
|
('B', 'B 值机岛'),
|
||
|
|
('C', 'C 值机岛');
|
||
|
|
|
||
|
|
INSERT INTO orms_checkindesk (CHECKINDESK_CODE, CCAT, CHECKIN_GROUP_CODE, CHECKINDESK_NAME, CHECKINDESK_STATUS, CHUT_CODE, CTRA, NEXTCHECKINDESK_CODE, OPERATE, TERMINAL_AREA_CODE) VALUES
|
||
|
|
('C05', 'D', 'C', 'C05 值机柜台', 'E', 'CH04', 'N', NULL, 1, 'T2C'),
|
||
|
|
('C06', 'D', 'C', 'C06 值机柜台', 'E', 'CH05', 'N', NULL, 1, 'T2C'),
|
||
|
|
('C07', 'D', 'C', 'C07 值机柜台', 'E', NULL, 'Y', 'C06', 1, 'T2C');
|
||
|
|
|
||
|
|
INSERT INTO orms_carousel (CAROUSEL_CODE, ACTION_ORDER, BCLS, CAROUSEL_NAME, CAROUSEL_STATUS, ICON_LENGTH, ICON_WIDTH, POS_X, POS_Y, STAND_LAYOUTED, TERMINAL_AREA_CODE) VALUES
|
||
|
|
('4', 1, 'D', '4 号行李转盘', 'E', 40, 20, 120, 400, 1, 'T2C'),
|
||
|
|
('5', 2, 'D', '5 号行李转盘', 'E', 40, 20, 160, 400, 1, 'T2C');
|