-- 机票汇总表
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_PLANE_SUMMARY')
CREATE TABLE T_ER_PLANE_SUMMARY
(
    FID                   BIGINT          DEFAULT 0   NOT NULL,
    FSUMMARYAMOUNT        DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FSUMMARYDISCOUNT      DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FORGID                VARCHAR(50)     DEFAULT ' ' NOT NULL,
    FSUMMARYSTART         DATETIME,
    FSUMMARYEND           DATETIME,
    FOTHERDATASUMMARY     VARCHAR(255)    DEFAULT ' ' NOT NULL,
    FOTHERDATASUMMARY_TAG NCLOB           DEFAULT ' ',
    FFULLTICKETSUMMARY    VARCHAR(255)    DEFAULT ' ' NOT NULL,
    FFULLTICKETSUMMARY_TAG NCLOB           DEFAULT ' ',
    FORGTYPE              VARCHAR(50)     DEFAULT ' ' NOT NULL
);

-- 酒店汇总表
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_HOTEL_SUMMARY')
CREATE TABLE T_ER_HOTEL_SUMMARY
(
    FID                  BIGINT          DEFAULT 0   NOT NULL,
    FSUMMARYAMOUNT       DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FORGID               VARCHAR(50)     DEFAULT ' ' NOT NULL,
    FSUMMARYEND          DATETIME,
    FSUMMARYSTART        DATETIME,
    FTRAVELUSER          VARCHAR(255)    DEFAULT ' ' NOT NULL,
    FTRAVELUSER_TAG      NCLOB           DEFAULT ' ',
    FORGTYPE              VARCHAR(50)     DEFAULT ' ' NOT NULL,
    FOTHERDATASUMMARY     VARCHAR(255)    DEFAULT ' ' NOT NULL,
    FOTHERDATASUMMARY_TAG NCLOB           DEFAULT ' '
);

-- 火车汇总表
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_TRAIN_SUMMARY')
CREATE TABLE T_ER_TRAIN_SUMMARY
(
    FID                  BIGINT          DEFAULT 0   NOT NULL,
    FSUMMARYAMOUNT       DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FSUMMARYSERVICEFEE   DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FSUMMARYREFUNDAMOUNT DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FORGID               VARCHAR(50)     DEFAULT ' ' NOT NULL,
    FSUMMARYEND          DATETIME,
    FSUMMARYSTART        DATETIME,
    FORDERNUMS           VARCHAR(50)     DEFAULT ' ' NOT NULL,
    FSUMMARYACTIVEPEOPLE VARCHAR(50)     DEFAULT ' ' NOT NULL,
    FTRAVELUSER          VARCHAR(255)    DEFAULT ' ' NOT NULL,
    FTRAVELUSER_TAG      NCLOB           DEFAULT ' ',
    FORGTYPE              VARCHAR(50)     DEFAULT ' ' NOT NULL,
    FOTHERDATASUMMARY     VARCHAR(255)    DEFAULT ' ' NOT NULL,
    FOTHERDATASUMMARY_TAG NCLOB           DEFAULT ' '
);

-- 用车汇总表
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_VEHICLE_SUMMARY')
CREATE TABLE T_ER_VEHICLE_SUMMARY
(
    FID                  BIGINT          DEFAULT 0   NOT NULL,
    FSUMMARYAMOUNT       DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FSUMMARYAVMILEPRICE  DECIMAL(23, 10) DEFAULT 0   NOT NULL,
    FVEHICLETYPE         VARCHAR(50)     DEFAULT ' ' NOT NULL,
    FORGID               VARCHAR(50)     DEFAULT ' ' NOT NULL,
    FSUMMARYEND          DATETIME,
    FSUMMARYSTART        DATETIME,
    FORDERNUMS           VARCHAR(50)     DEFAULT ' ' NOT NULL,
    FSUMMARYACTIVEPEOPLE VARCHAR(50)     DEFAULT ' ' NOT NULL,
    FTRAVELUSER          VARCHAR(255)    DEFAULT ' ' NOT NULL,
    FTRAVELUSER_TAG      NCLOB           DEFAULT ' ',
    FORGTYPE              VARCHAR(50)     DEFAULT ' ' NOT NULL,
    FOTHERDATASUMMARY     VARCHAR(255)    DEFAULT ' ' NOT NULL,
    FOTHERDATASUMMARY_TAG NCLOB           DEFAULT ' '
);

-- 出行汇总表
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_CITY_ROUTES_SUMMARY')
CREATE TABLE T_ER_CITY_ROUTES_SUMMARY
(
    FID           BIGINT       DEFAULT 0   NOT NULL,
    FSUMMARYSTART DATETIME,
    FSUMMARYEND   DATETIME,
    FORGID        VARCHAR(50)  DEFAULT ' ' NOT NULL,
    FDATATYPE     VARCHAR(50)  DEFAULT ' ' NOT NULL,
    FCITYJSON     VARCHAR(255) DEFAULT ' ' NOT NULL,
    FCITYJSON_TAG NCLOB        DEFAULT ' '
);

-- 经纬度
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_LON_LAT')
CREATE TABLE T_ER_LON_LAT
(
    FID        BIGINT       DEFAULT 0   NOT NULL,
    FNUMBER    VARCHAR(255) DEFAULT ' ' NOT NULL,
    FCITYNAME  VARCHAR(255) DEFAULT ' ' NOT NULL,
    FLONGITUDE VARCHAR(255) DEFAULT ' ' NOT NULL,
    FLATITUDE  VARCHAR(255) DEFAULT ' ' NOT NULL
);

-- 指标结果集
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_INDEX_RESULT')
CREATE TABLE T_ER_INDEX_RESULT
(
    FID         BIGINT       DEFAULT 0   NOT NULL,
    FCACHEKEY   VARCHAR(255) DEFAULT ' ' NOT NULL,
    FRESULT     VARCHAR(255) DEFAULT ' ' NOT NULL,
    FRESULT_TAG NCLOB        DEFAULT ' ',
    FSTARTTIME  TIMESTAMP,
    FENDTIME    TIMESTAMP,
    FORGID      VARCHAR(50)  DEFAULT ' ' NOT NULL
);
-- 费用同比汇总表
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_YOY_SUMMARY')
CREATE TABLE T_ER_YOY_SUMMARY
(
    FID                   BIGINT          DEFAULT 0   NOT NULL,
    FORGID                VARCHAR(50)     DEFAULT ' ' NOT NULL,
    FSUMMARYSTART         DATETIME,
    FSUMMARYEND           DATETIME,
    FOTHERDATASUMMARY     VARCHAR(255)    DEFAULT ' ' NOT NULL,
    FOTHERDATASUMMARY_TAG NCLOB           DEFAULT ' ',
    FORGTYPE              VARCHAR(50)     DEFAULT ' ' NOT NULL,
    FSUMMARYYEAR              VARCHAR(50)     DEFAULT ' ' NOT NULL
);

EXEC P_ALTERPK 'PK_T_ER_PLANE_SUMMARY', 'T_ER_PLANE_SUMMARY', 'FID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_PLANE_SUMMARY')
CREATE INDEX IDX_ER_PLANE_SUMMARY ON T_ER_PLANE_SUMMARY (FSUMMARYSTART, FSUMMARYEND, FORGID);


EXEC P_ALTERPK 'PK_T_ER_HOTEL_SUMMARY', 'T_ER_HOTEL_SUMMARY', 'FID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_HOTEL_SUMMARY')
CREATE INDEX IDX_ER_HOTEL_SUMMARY ON T_ER_HOTEL_SUMMARY (FSUMMARYSTART, FSUMMARYEND, FORGID);


EXEC P_ALTERPK 'PK_T_ER_TRAIN_SUMMARY', 'T_ER_TRAIN_SUMMARY', 'FID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_TRAIN_SUMMARY')
CREATE INDEX IDX_ER_TRAIN_SUMMARY ON T_ER_TRAIN_SUMMARY (FSUMMARYSTART, FSUMMARYEND, FORGID);


EXEC P_ALTERPK 'PK_T_ER_VEHICLE_SUMMARY', 'T_ER_VEHICLE_SUMMARY', 'FID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_VEHICLE_SUMMARY')
CREATE INDEX IDX_ER_VEHICLE_SUMMARY ON T_ER_VEHICLE_SUMMARY (FSUMMARYSTART, FSUMMARYEND, FORGID);


EXEC P_ALTERPK 'PK_T_ER_CITY_ROUTES_SUMMARY', 'T_ER_CITY_ROUTES_SUMMARY', 'FID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_CITY_ROUTES_SUMMARY')
CREATE INDEX IDX_ER_CITY_ROUTES_SUMMARY ON T_ER_CITY_ROUTES_SUMMARY (FSUMMARYSTART, FSUMMARYEND, FORGID);


EXEC P_ALTERPK 'PK_T_ER_LON_LAT', 'T_ER_LON_LAT', 'FID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_LON_LAT')
CREATE INDEX IDX_ER_LON_LAT ON T_ER_LON_LAT (FCITYNAME);

EXEC P_ALTERPK 'PK_T_ER_INDEX_RESULT', 'T_ER_INDEX_RESULT', 'FID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_INDEX_RESULT_CACHE')
CREATE INDEX IDX_ER_INDEX_RESULT_CACHE ON T_ER_INDEX_RESULT (FCACHEKEY);

IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_INDEX_RESULT_TIME')
CREATE INDEX IDX_ER_INDEX_RESULT_TIME ON T_ER_INDEX_RESULT (FSTARTTIME,FENDTIME,FORGID);

EXEC P_ALTERPK 'PK_T_ER_YOY_SUMMARY', 'T_ER_YOY_SUMMARY', 'FID', '1';

IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_YOY_SUMMARY')
CREATE INDEX IDX_ER_YOY_SUMMARY ON T_ER_YOY_SUMMARY (FSUMMARYSTART, FSUMMARYEND, FORGID);

IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_YOY_SUMMARYYEAR')
CREATE INDEX IDX_ER_YOY_SUMMARYYEAR ON T_ER_YOY_SUMMARY (FSUMMARYYEAR, FORGID);


