Hastus import data correnspondence - HSLdevcom/jore4 GitHub Wiki

Data format when importing from hastus is the following

erDiagram

    ApplicationRecord {
        hastusversio vsc_exp_ver
        pvm JobDate
        aika md_job_time
    }

    BookingRecord {
        booking vsc_booking
        kuvaus vsc_booking_desc
        aikataulunimi vsc_name
        pvmTyyppi md_vsc_sched_type
        alkuPvm VscSumStartDate
        loppuPvm VscSumEndDate
        liikennointisopimus md_vsc_controct
    }

    VehicleScheduleRecord {
        aikataulunimi vsc_name
        pvmTyyppi md_vsc_sched_type
        versionumero vsc_scenario
        alkuPvm VscSumStartDate
        loppuPvm VscSumEndDate
        muokkausTimeStamp md_vsc_time_stamp
    }

    BlockRecord {
        vuoron_sis_numero blk_int_number
        block_number blk_number
        nth_in_task blk_sequence
        hastusPaikka md_blk_place_start
        hastusPaikka md_blk_place_end
        linja blk_route
        valmisteluaika blk_prep_out_user
        valmisteluaika blk_prep_in_user
        aikataulutyyppi blk_vehicle_group
    }

    TripRecord {
        liikennointisopimus md_vsc_contract
        vuoron_sis_numero blk_int_number
        lahdon_sis_numero trp_int_number
        vuoron_numero trp_number
        lahto_tyyppi trp_pro_typ
        linja trp_route
        naytetty_linja trp_route_public
        varianttitunnus md_trp_via_variant
        lahtoaika trp_time_start
        loppuaika trp_time_end
        kesto Duration
        terminaaliaika trp_lay_min_start
        elpymisaika trp_lay_min_end
        pituus trp_distance
        paivatyyppi md_trp_note
        lisainfo md_trp_second_note
        suunta trp_direction
        kalustotyyppi md_trp_vehicle_group_1
        kalustopakko trp_veh_grp_mandatory
        varalahto md_trp_vara
        lisalahto md_trp_lisa
    }

    TripStopRecord {
        lahdon_sis_numero trp_int_number
        hastuspaikka md_tstp_place
        pysakkiId tstp_stop_id
        ohitusaika md_tstp_pass_time
        etaisyys_edelliselta tstp_distance
        pysakkityyppi md_tstp_stopcode
        ajantasaustysakki ttp_note
    }

    ApplicationRecord ||--|| BookingRecord : ""
    BookingRecord ||--|| VehicleScheduleRecord : ""
    VehicleScheduleRecord ||--o{ BlockRecord : ""
    BlockRecord ||--o{ TripRecord : ""
    TripRecord ||--o{ TripStopRecord : ""

Our current database model

erDiagram
vehicle_schedule_frame {
string name_i18n
date validity_start
date validity_end
}

    vehicle_service

    day_type

    block

    vehicle_journey

    timetabled_passing_time {
        time arrival_time
        time departure_time
    }

    scheduled_stop_point_in_journey_pattern_ref {
        string scheduled_stop_point_label
        ref scheduled_stop_point_label_passing
    }

    journey_pattern_ref

    vehicle_schedule_frame ||--o{ vehicle_service : ""
    vehicle_service }|--|{ day_type : ""
    vehicle_service ||--o{ block : ""
    block ||--o{ vehicle_journey : ""
    vehicle_journey ||--o{ timetabled_passing_time : ""
    journey_pattern_ref }o--o{ block : ""
    journey_pattern_ref ||--|| scheduled_stop_point_in_journey_pattern_ref : ""
    scheduled_stop_point_in_journey_pattern_ref ||--o{ timetabled_passing_time : ""

Data mapping correspondencies:

sequenceDiagram

    BookingRecord->>vehicle_schedule_frame: vsc_booking -> booking_label
    BookingRecord->>vehicle_schedule_frame: vsc_booking_desc -> booking_description
    BookingRecord->>vehicle_schedule_frame: VscSumStartDate -> validity_start
    BookingRecord->>vehicle_schedule_frame: VscSumEndDate -> validity_end
    BookingRecord->>vehicle_service: md_vsc_sched_type -> päivämäärätyyppi

    BookingRecord->>vehicle_schedule_frame: md_vsc_controct -> sopimustiedot TODO?

sequenceDiagram

    BlockRecord->>block: blk_prep_out_user -> preparing_time
    BlockRecord->>block: blk_prep_in_user -> finishing_time
    BlockRecord->>block: blk_vehicle_group -> vehicle_type
    BlockRecord->>vehicle_service: blk_number -> name_i18n

    BlockRecord->>metadata: md_blk_place_start -> start hastus place?
    BlockRecord->>metadata: md_blk_place_end -> end hastus place?
    BlockRecord->>metadata: blk_route -> main line

sequenceDiagram

    TripRecord->>vehicle_journey: trp_route_public -> displayed_name
    TripRecord->>vehicle_journey: trp_lay_min_start -> turnaround_time
    TripRecord->>vehicle_journey: trp_lay_min_end -> layover_time
    TripRecord->>vehicle_journey: md_trp_vehicle_group_1 -> vehicle_type
    TripRecord->>vehicle_journey: trp_veh_grp_mandatory -> is_vehicle_type_mandatory
    TripRecord->>vehicle_journey: md_trp_vara -> is_backup_journey
    TripRecord->>vehicle_journey: md_trp_lisa -> is_extra_journey

    TripRecord->>vehicle_journey: md_vsc_contract -> sopimustiedot TODO?

    TripRecord->>metadata: trp_number -> lähdön numero
    TripRecord->>metadata: trp_pro_typ -> lähdön tyyppi
    TripRecord->>metadata: trp_route -> line
    TripRecord->>metadata: md_trp_via_variant -> route via variant
    TripRecord->>metadata: trp_time_start -> start time
    TripRecord->>metadata: trp_time_end -> end time
    TripRecord->>metadata: Duration -> duration
    TripRecord->>metadata: trp_distance -> total distance
    TripRecord->>metadata: md_trp_note -> day type note
    TripRecord->>metadata: md_trp_second_note -> trip note 2?
    TripRecord->>metadata: trp_direction -> direction

sequenceDiagram

    TripStopRecord->>timetabled_passing_time: md_tstp_place -> hastuspaikka
    TripStopRecord->>timetabled_passing_time: tstp_stop_id -> stop_id
    TripStopRecord->>timetabled_passing_time: md_tstp_pass_time -> arrival_time
    TripStopRecord->>timetabled_passing_time: tstp_distance -> distance from previous stop
    TripStopRecord->>timetabled_passing_time: md_tstp_stopcode -> pysäkin tyyppi
    TripStopRecord->>timetabled_passing_time: ttp_note -> ajantasauspysäkkitiedot