Structure de la base de données

From Documentation de la solution web de gestion OpenFlyers
Jump to navigation Jump to search

Schéma de la structure de la base de données

Structure

create table account
(
    id               int unsigned auto_increment
        primary key,
    creation_date    date                          null,
    name             text                          null,
    export_account   varchar(255)                  null,
    activated        tinyint(1) unsigned default 1 not null,
    category         tinyint(1) unsigned default 0 null,
    account_type     tinyint(1) unsigned default 0 null,
    owner_id         int unsigned        default 0 not null,
    payment_allowed  tinyint(1)          default 0 null,
    budget_id        int unsigned                  null,
    order_num        int                           null,
    accounting_id    int unsigned                  null,
    deactivated_date datetime                      null,
    group_sales      tinyint(1)          default 0 not null
)
    comment 'List of account';

create index idx_owner_id
    on account (owner_id);

CREATE TABLE `account_entry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `flow_id` int(11) DEFAULT NULL,
  `account_date` datetime DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `credit` decimal(15,5) DEFAULT '0.00000',
  `debit` decimal(15,5) DEFAULT '0.00000',
  `payment_type` int(10) unsigned DEFAULT NULL,
  `payment_description` text,
  `person_delivery_id` int(10) unsigned DEFAULT NULL,
  `comments` text,
  `validated` int(1) DEFAULT '0',
  `exported` int(1) DEFAULT '0',
  `registration_date` datetime DEFAULT NULL,
  `budget_id` int(10) unsigned DEFAULT NULL,
  `product_id` int(10) unsigned DEFAULT NULL,
  `signature` varchar(56) DEFAULT NULL,
  `signature_date` bigint(13) unsigned DEFAULT NULL,
  `lettering` int(11) DEFAULT NULL,
  `lettering_date` datetime DEFAULT NULL,
  `flow_reversal_id` int(10) unsigned DEFAULT NULL,
  `transaction_id` text,
  PRIMARY KEY (`id`),
  KEY `idx_flow_id` (`flow_id`),
  KEY `idx_account_date` (`account_date`),
  KEY `idx_signature_date` (`signature_date`),
  KEY `flow_reversal_id_idx` (`flow_reversal_id`)
);

create table account_link
(
    account_id        int unsigned default 0 not null,
    linked_account_id int unsigned default 0 not null,
    primary key (account_id, linked_account_id)
);

create table account_type
(
    id                     int unsigned auto_increment
        primary key,
    name                   varchar(255)                  null,
    category               tinyint(1) unsigned default 0 null,
    activated              tinyint(1)          default 1 null,
    order_num              int                           null,
    accounting_id          int unsigned                  null,
    export_account_pattern varchar(255)                  null
);

create table account_type_profile
(
    account_type_id int unsigned    default 0 not null,
    profile_id      bigint unsigned default 0 not null,
    primary key (account_type_id, profile_id)
)
    comment 'Linked account type and profile';

create table accounting
(
    id                int unsigned auto_increment
        primary key,
    name              varchar(255)           null,
    unit_name         varchar(255)           null,
    symbol            varchar(255)           null,
    format            tinyint(1)             null,
    decimal_precision int unsigned default 2 null
);

create table accounting_profile
(
    accounting_id int unsigned    default 0 not null,
    profile_id    bigint unsigned default 0 not null,
    primary key (accounting_id, profile_id)
);

create table activity_type
(
    id        bigint unsigned default 0 not null
        primary key,
    name      varchar(255)              null,
    order_num int                       null,
    activated tinyint(1)      default 1 not null,
    color     int unsigned              null,
    training  tinyint(1)      default 0 not null
)
    comment 'list of flight type';

create table aircraft
(
    id              int unsigned     default 0     not null
        primary key,
    ref_date        datetime                       null,
    ref_hours       int                            null,
    inspection_date datetime                       null,
    inspection_time int                            null,
    tolerance_time  int(11) unsigned default 6000  null,
    last_counter    int                            null,
    interval_visit  int(11) unsigned default 30000 null,
    time_alert1     int              default 6000  null,
    time_alert2     int              default 0     null,
    time_alert3     int              default -3000 null,
    day_alert1      smallint(3)      default 15    null,
    day_alert2      smallint(3)      default 0     null,
    day_alert3      smallint(3)      default -8    null
);

create table aircraft_type
(
    id                   int unsigned auto_increment
        primary key,
    flight_time_formula  varchar(255)        default '%DURATION' null,
    counter_state        tinyint(2)          default -1          not null,
    tolerance            int                 default 0           null,
    autonomy             int                 default 5990        null,
    true_air_speed       int unsigned        default 0           not null,
    digit_counter_number tinyint(1) unsigned default 4           not null
)
    comment 'Types of aircraft';

create table aircraft_type_allowed_status
(
    aircraft_type_id int unsigned null,
    place_num        int unsigned null,
    status_id        int unsigned null
)
    comment 'List of allowed functions for each aircraft type';

create table aircraft_type_mandatory_flight_type
(
    aircraft_type_id int unsigned    null,
    activity_type_id bigint unsigned null
)
    comment 'List of compulsory flight type for each aircraft type';

create table aircraft_type_uncomp_flight_type
(
    aircraft_type_id int unsigned    null,
    activity_type_id bigint unsigned null
)
    comment 'List of uncompatible flight type for each aircraft type';

create table aircraft_type_validity_type
(
    aircraft_type_id int unsigned default 0 not null,
    validity_type_id int unsigned default 0 not null,
    check_num        int unsigned default 0 not null,
    primary key (aircraft_type_id, validity_type_id, check_num)
)
    comment 'Types of qualif required for each aircraft type';

create table allocation_rule
(
    id         int unsigned auto_increment
        primary key,
    title      varchar(255)         not null,
    account_id int(10)              not null,
    keyword    varchar(255)         null,
    order_num  int(10)              not null,
    amount     tinyint(1)           not null,
    mandatory  tinyint(1) default 0 not null
);

create table balance
(
    account_id      int unsigned                   not null,
    balance_date_id int unsigned                   not null,
    debit           decimal(15, 5) default 0.00000 null,
    credit          decimal(15, 5) default 0.00000 null,
    primary key (account_id, balance_date_id)
);

create table balance_date
(
    id           int unsigned auto_increment
        primary key,
    balance_date datetime null
);

create table booking
(
    id               int unsigned auto_increment
        primary key,
    booking_group_id int unsigned null,
    start_date       datetime     null,
    end_date         datetime     null
);

create table booking_account_entry
(
    booking_id            int unsigned default 0 not null,
    account_entry_flow_id int unsigned default 0 not null,
    primary key (booking_id, account_entry_flow_id)
);

create table booking_activity_type
(
    booking_id       int unsigned default 0 not null,
    activity_type_id int unsigned default 0 not null,
    primary key (booking_id, activity_type_id)
);

create table booking_changelog
(
    id             int unsigned auto_increment
        primary key,
    booking_id     int unsigned not null,
    person_id      int unsigned not null,
    changelog_date datetime     not null,
    booking_state  text         null
);

create table booking_person
(
    booking_id int unsigned default 0 not null,
    person_id  int unsigned default 0 not null,
    place_num  int unsigned           null,
    status_id  int unsigned           null,
    primary key (booking_id, person_id)
);

create table booking_resource
(
    booking_id  int unsigned default 0 not null,
    resource_id int unsigned default 0 not null,
    primary key (booking_id, resource_id)
);

CREATE TABLE `booking_training_program` (
  `booking_group_id` int(10) unsigned NOT NULL,
  `training_program_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`booking_group_id`,`training_program_id`),
  CONSTRAINT `booking_training_program_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`)
);

create table budget
(
    id          int unsigned auto_increment
        primary key,
    group_id    int unsigned default 0 not null,
    name        varchar(255)           null,
    order_num   int                    null,
    export_code varchar(255)           null
);

create table budget_group
(
    id        int unsigned auto_increment
        primary key,
    name      varchar(255) null,
    order_num int          null
);

CREATE TABLE `business_field` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `variable` varchar(255) DEFAULT NULL,
  `label` varchar(255) NOT NULL,
  `value_type` varchar(40) NOT NULL,
  `category` varchar(255) NOT NULL,
  `order_num` int(11) NOT NULL,
  `dsn` varchar(20) DEFAULT 'customer',
  `compulsory_fill` tinyint(1) DEFAULT '0',
  `linked_category` varchar(255) DEFAULT NULL,
  `linked_business_field_id` int(10) unsigned DEFAULT NULL,
  `linked_field_name` varchar(255) DEFAULT NULL,
  `max_display` int(10) DEFAULT '-1',
  `formula` text,
  `default_value` text,
  `placeholder` text,
  `access_level_required` tinyint(4) NOT NULL DEFAULT '0',
  `user_access_mode` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `variable` (`variable`),
  KEY `business_field_linked_business_field_id_fk` (`linked_business_field_id`),
  CONSTRAINT `business_field_linked_business_field_id_fk` FOREIGN KEY (`linked_business_field_id`) REFERENCES `business_field` (`id`)
) COMMENT='List of extra form field';

create table business_field_activity_type
(
    business_field_id       int unsigned    default 0 not null,
    activity_type_id        bigint unsigned default 0 not null,
    business_field_group_id int unsigned    default 0 not null,
    visibility_type         tinyint(1) unsigned       null,
    primary key (business_field_id, activity_type_id)
);

create table business_field_content
(
    category_id       int unsigned           not null,
    business_field_id int unsigned default 0 not null,
    person_id         int unsigned default 0 not null,
    content           text                   null,
    placeholder       text                   null,
    primary key (category_id, business_field_id, person_id)
)
    comment 'Content of extra form field';

create table business_field_group
(
    id        int unsigned auto_increment
        primary key,
    label     varchar(255) null,
    order_num int          null
);

create table criteria
(
    id        int auto_increment
        primary key,
    label     varchar(255)                   not null,
    predicate text                           not null,
    order_num int                            null,
    dsn       varchar(20) default 'customer' null
);

create table criteria_profile
(
    criteria_id int unsigned    not null,
    profile_id  bigint unsigned not null,
    primary key (criteria_id, profile_id)
);

create table customer_bill_entry
(
    id                    int unsigned auto_increment
        primary key,
    flow_id               int unsigned                   null,
    account_entry_id      int unsigned                   null,
    account_entry_flow_id int unsigned                   null,
    owner_category        int unsigned                   null,
    owner_id              int unsigned                   null,
    customer_bill_id      int unsigned                   null,
    product_id            int unsigned                   null,
    qty                   decimal(15, 2) default 0.00    null,
    unit_price            decimal(15, 5) default 0.00000 null,
    debit                 decimal(15, 5) default 0.00000 null,
    credit                decimal(15, 5) default 0.00000 null,
    bill_date             datetime                       null
);

create index idx_account_entry_flow_id
    on customer_bill_entry (account_entry_flow_id);

create index idx_account_entry_id
    on customer_bill_entry (account_entry_id);

create table customer_bill_file
(
    id        int unsigned auto_increment
        primary key,
    file_type varchar(255) null,
    content   longblob     null
)
    comment 'List of customer bill stored file';

create table customer_receipt_entry
(
    id                    int unsigned auto_increment
        primary key,
    flow_id               int unsigned                   null,
    account_entry_id      int unsigned                   null,
    account_entry_flow_id int unsigned                   null,
    owner_category        int unsigned                   null,
    owner_id              int unsigned                   null,
    payment_type_id       int unsigned                   null,
    customer_receipt_id   int unsigned                   null,
    debit                 decimal(15, 5) default 0.00000 null,
    credit                decimal(15, 5) default 0.00000 null,
    receipt_date          datetime                       null,
    owner_email           varchar(255)                   null
);

create index idx_account_entry_flow_id
    on customer_receipt_entry (account_entry_flow_id);

create index idx_account_entry_id
    on customer_receipt_entry (account_entry_id);

create table customer_receipt_file
(
    id        int unsigned auto_increment
        primary key,
    file_type varchar(255) null,
    content   longblob     null
);

create table default_display
(
    person_id     int unsigned            not null,
    display_key   varchar(255) default '' not null,
    display_value text                    null,
    primary key (person_id, display_key)
);

create table default_multi_display
(
    resource_type char(30)     not null,
    person_id     int unsigned not null,
    resource_id   varchar(100) not null,
    display_value text         null,
    primary key (resource_type, person_id, resource_id)
);

create table email_sent
(
    id          bigint unsigned auto_increment
        primary key,
    message_id  varchar(150)     not null,
    sender      varchar(255)     not null,
    title       varchar(255)     not null,
    category    tinyint unsigned not null,
    category_id int unsigned     not null,
    constraint message_id_UNIQUE
        unique (message_id)
);

create table email_sent_recipient
(
    id                   bigint unsigned auto_increment
        primary key,
    email_sent_id        bigint unsigned  not null,
    person_id            int unsigned     not null,
    email                varchar(255)     null,
    recipient_type       tinyint unsigned null,
    smtp_status_category varchar(30)      null,
    email_status         tinyint unsigned null,
    update_date          datetime         not null
);

create index email_sent_id_idx
    on email_sent_recipient (email_sent_id);

create table exceptionnal_inst_date
(
    id         int unsigned auto_increment
        primary key,
    person_id  int unsigned        null,
    start_date datetime            null,
    end_date   datetime            null,
    presence   tinyint(1) unsigned null
);

create table extra_field_profile
(
    business_field_id                  int unsigned    default 0 not null,
    booking_popup_display_4_profile_id bigint unsigned default 0 not null,
    primary key (business_field_id, booking_popup_display_4_profile_id)
)
    comment 'Linked extra field and profile';

create table facebook
(
    id          int unsigned auto_increment
        primary key,
    category    int unsigned not null,
    owner_id    int unsigned not null,
    small       blob         null,
    original    mediumblob   not null,
    label       text         null,
    description text         null
);

create table favorite_icao
(
    icao varchar(6) default '' not null
        primary key
)
    comment 'favorite airfield list';

create table favorite_report
(
    report_id int unsigned not null
        primary key
);

create table favorite_report_business_field
(
    report_id         int unsigned not null,
    business_field_id int unsigned not null,
    default_value     text         null,
    primary key (report_id, business_field_id)
);

create table favorite_report_profile
(
    report_id  int unsigned              not null,
    profile_id bigint unsigned default 0 not null,
    primary key (report_id, profile_id)
);

create table fhp_aircraft_type
(
    fhp_id           int(10) default 0 not null,
    aircraft_type_id int(10) default 0 not null,
    primary key (fhp_id, aircraft_type_id)
)
    comment 'flight hour pricing aircraft type list';

create table fhp_flight_type
(
    fhp_id           int(10)             default 0 not null,
    activity_type_id bigint unsigned     default 0 not null,
    excluded         tinyint(1) unsigned default 0 null,
    primary key (fhp_id, activity_type_id)
)
    comment 'flight hour pricing flight type list';

create table fhp_profile
(
    fhp_id     int unsigned                  not null,
    profile_id bigint unsigned               not null,
    place_num  tinyint(1) unsigned default 0 not null,
    primary key (fhp_id, profile_id, place_num)
);

create table file
(
    id            int unsigned auto_increment
        primary key,
    name          varchar(255)        null,
    content       longblob            null,
    file_type     varchar(255)        null,
    category      tinyint(1) unsigned null,
    owner_id      int(10)             null,
    file_group_id int(10)             null
)
    comment 'List of stored file';

create table file_group
(
    id    int unsigned auto_increment
        primary key,
    label varchar(255) null
);

create table flight
(
    id                    int unsigned auto_increment
        primary key,
    aircraft_id           int unsigned               null,
    start_date            datetime                   null,
    duration              int                        null,
    activity_type_id      bigint unsigned            null,
    people_onboard        int unsigned               null,
    departure_location_id int unsigned               null,
    arrival_location_id   int unsigned               null,
    counter_departure     int unsigned               null,
    counter_arrival       int unsigned               null,
    landing_number        int(10)                    null,
    airborne              tinyint(1) unsigned        null,
    validated             tinyint unsigned default 0 null,
    departure_icao_id     varchar(6)                 null,
    arrival_icao_id       varchar(6)                 null
)
    comment 'list of flight';

create table flight_account_entry
(
    flight_id        int unsigned default 0 not null,
    account_entry_id int unsigned default 0 not null,
    primary key (flight_id, account_entry_id)
)
    comment 'flight account entry join';

create index idx_account_entry_id
    on flight_account_entry (account_entry_id);

create table flight_hours_pricing
(
    id                 int unsigned auto_increment
        primary key,
    name               text                          null,
    price_formula      text                          null,
    left_account_id    int unsigned                  null,
    right_account_id   int unsigned                  null,
    left_account_type  tinyint(1) unsigned default 0 null,
    right_account_type tinyint(1) unsigned default 0 null,
    order_num          int                           null,
    credit_budget_id   int unsigned                  null,
    debit_budget_id    int unsigned                  null,
    product_id         int unsigned                  null,
    sale_trigger_id    int unsigned        default 0 null,
    query              text                          null,
    variable_formula   text                          null,
    business_field_id  int unsigned                  null,
    debit_bill_num     int unsigned                  null,
    credit_bill_num    int unsigned                  null,
    qty_formula        text                          null,
    unit_price_formula text                          null
)
    comment 'list of flight hours pricing formula';

create table flight_pilot
(
    flight_id int unsigned           not null,
    pilot_id  int unsigned           not null,
    status_id int unsigned           null,
    num       int unsigned default 0 not null,
    primary key (flight_id, pilot_id, num)
)
    comment 'list of crew for each flight';

create table flight_tank_qty
(
    id           int unsigned auto_increment
        primary key,
    flight_id    int unsigned                not null,
    tank_id      int unsigned                not null,
    quantity     varchar(255) default '0.00' not null,
    after_flight tinyint(1)   default 0      not null,
    account_id   int unsigned                null,
    pay_type     tinyint(1) unsigned         null
);

CREATE TABLE `flight_track` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `flight_id` int(10) NOT NULL,
  `track` mediumblob NOT NULL,
  `file_type` varchar(255) DEFAULT 'kml',
  PRIMARY KEY (`id`)
);

create table flight_type_mandatory_validity_type
(
    activity_type_id bigint unsigned default 0 not null,
    validity_type_id int unsigned    default 0 not null,
    primary key (activity_type_id, validity_type_id)
)
    comment 'list of mandatory qualification for each flight type';

create table import
(
    id                  int unsigned auto_increment
        primary key,
    label               varchar(255)                          not null,
    order_num           int                                   null,
    import_file_type    varchar(255) default 'csv-comma-CRLF' not null,
    match_query         text                                  null,
    uptodate_test_query text                                  null,
    update_query        text                                  null,
    update_activated    tinyint(1)   default 0                not null,
    login               varchar(255)                          null,
    hash_password       varchar(255)                          null,
    sync_task_name      varchar(255)                          null
)
    comment 'List of import';

create table ip_stopped
(
    id          int unsigned auto_increment
        primary key,
    ip          varchar(255)        not null,
    counter     tinyint(1) unsigned not null,
    expire_date datetime            not null
)
    comment 'BLACKLISTED IP';

create table journal
(
    id        int unsigned auto_increment
        primary key,
    login     varchar(255)           null,
    date_log  datetime               null,
    rights    text                   null,
    rights2   text                   null,
    action    varchar(255)           null,
    person_id int unsigned default 0 null
)
    comment 'list of logs';

create index idx_action
    on journal (action);

create index idx_date_log
    on journal (date_log);

create table key_alert
(
    id         int unsigned auto_increment
        primary key,
    alert_date datetime               null,
    status     int unsigned default 0 not null
)
    comment 'Key alerts';

create table key_assignment
(
    key_id      tinyint(2) unsigned default 0 not null
        primary key,
    key_name    tinytext                      null,
    aircraft_id int unsigned        default 0 null,
    key_state   tinyint(1) unsigned default 0 null,
    key_word    bigint unsigned     default 0 null
);

create table key_host
(
    id       tinyint(2) auto_increment
        primary key,
    timeout  tinyint(2)          default 10          not null,
    num_key  tinyint(2) unsigned default 10          not null,
    ipkey    varchar(50)         default '127.0.0.1' not null,
    httpport int                 default 4080        not null
);

create table key_log
(
    id        int unsigned auto_increment
        primary key,
    log_date  datetime     null,
    action    varchar(255) null,
    message   varchar(255) null,
    key_id    int unsigned null,
    person_id int unsigned null,
    xmlrpc    int unsigned not null,
    error     varchar(255) null
)
    comment 'Key logs';

create table location
(
    icao_name       varchar(6)       not null
        primary key,
    name            varchar(64)      not null,
    latitude        double           null,
    longitude       double           null,
    altitude        int(7)           null,
    weather_station int(1) unsigned  null,
    asked_counter   bigint default 0 not null
)
    comment 'airfields coord';

create table log
(
    journal_id  int unsigned not null,
    action      varchar(255) null,
    table_name  varchar(255) null,
    field_name  varchar(255) null,
    field_value varchar(255) null
)
    comment 'part of logs';

create index idx_field_value
    on log (field_value);

create index idx_journal_id
    on log (journal_id);

create index journal_id
    on log (journal_id);

create table logger
(
    id            int unsigned auto_increment
        primary key,
    serial_number int unsigned                      null,
    color         varchar(20)         default 'red' not null,
    activated     tinyint(1) unsigned default 1     not null,
    order_num     int unsigned                      not null
)
    comment 'Logger parameters';

create table login_stopped
(
    id          int unsigned auto_increment
        primary key,
    login       varchar(255)     not null,
    counter     tinyint unsigned not null,
    expire_date datetime         not null
)
    comment 'BLACKLISTED LOGIN';

create table m_component
(
    id                  int unsigned auto_increment
        primary key,
    parent_id           int unsigned        default 0 not null,
    m_component_type_id int unsigned                  null,
    resource_id         int unsigned                  null,
    order_num           int                           not null,
    serial_number       varchar(255)                  null,
    brandnew_date       datetime                      null,
    activated           tinyint(1) unsigned default 1 not null
);

create table m_component_type
(
    id                     int unsigned auto_increment
        primary key,
    parent_id              int unsigned        default 0 not null,
    resource_type_id       int                           null,
    m_classification_id    int                           null,
    is_maintenance_check   tinyint(1) unsigned default 1 not null,
    order_num              int                           not null,
    label                  varchar(255)                  null,
    description            varchar(255)                  null,
    manufacturer           varchar(255)                  null,
    manufacturer_reference varchar(255)                  null,
    part_number            varchar(255)                  null,
    periodicity            int unsigned                  null,
    tolerance              int unsigned        default 0 not null,
    calendar_periodicity   int unsigned                  null,
    calendar_tolerance     int unsigned        default 0 not null,
    activated              tinyint(1) unsigned default 1 not null
);

create table m_component_type_parentality
(
    m_component_type_id        int unsigned not null,
    m_component_type_parent_id int unsigned not null,
    primary key (m_component_type_id, m_component_type_parent_id)
);

create table m_history
(
    id                     int unsigned auto_increment
        primary key,
    m_component_id         int unsigned                  not null,
    install_date           datetime                      null,
    remove_date            datetime                      null,
    total_hours_on_install int unsigned        default 0 not null,
    threshold_hours        int unsigned        default 0 not null,
    threshold_date         datetime                      null,
    threshold_date_locked  tinyint(1) unsigned default 0 not null,
    threshold_hours_locked tinyint(1) unsigned default 0 not null
);

create table m_operation
(
    id                  int unsigned auto_increment
        primary key,
    content             text         null,
    m_component_type_id int unsigned not null,
    order_num           int          not null
);

create table maintenance_history
(
    id                                   int unsigned auto_increment
        primary key,
    resource_id                          int unsigned                  null,
    maintenance_program_id               int unsigned                  null,
    frame_hours_at_work_start            int(10)                       null,
    date_work_end                        date                          null,
    min_overhaul_counter                 int(10)                       null,
    max_overhaul_counter                 int(10)                       null,
    min_overhaul_date                    date                          null,
    max_overhaul_date                    date                          null,
    min_next_overhaul_counter            int(10)                       null,
    max_next_overhaul_counter            int(10)                       null,
    min_next_overhaul_date               date                          null,
    max_next_overhaul_date               date                          null,
    first_reference_visit                tinyint(1) unsigned default 0 not null,
    is_visit                             tinyint(1) unsigned default 0 not null,
    action_type                          tinyint(1) unsigned           null,
    previous_line_linked_time_action     int(10)                       null,
    previous_line_linked_calendar_action int(10)                       null,
    next_line_linked_time_action         int(10)                       null,
    next_line_linked_calendar_action     int(10)                       null,
    time_tolerance                       int(10)                       null,
    calendar_tolerance                   int(10)                       null,
    time_periodicity_component           int(10)                       null,
    calendar_periodicity_component       int(10)                       null,
    time_periodicity                     int(10)                       null,
    calendar_periodicity                 int(10)                       null,
    reference_overhaul_counter           int(10)                       null,
    reference_overhaul_date              date                          null
);

create table maintenance_program
(
    id                                    int unsigned auto_increment
        primary key,
    resource_id                           int unsigned                  null,
    component_type_id                     int unsigned                  null,
    description                           varchar(255)                  null,
    time_periodicity                      int(10)                       null,
    calendar_periodicity                  int(10)                       null,
    time_first_reference                  int(10)                       null,
    calendar_first_reference              date                          null,
    time_tolerance                        int(10)                       null,
    calendar_tolerance                    int(10)                       null,
    frame_hours_at_work_start             int(10)                       null,
    date_work_end                         date                          null,
    time_elapsed                          int(10)                       null,
    calendar_elapsed                      int(10)                       null,
    time_remaining                        int(10)                       null,
    calendar_remaining                    int(10)                       null,
    min_next_overhaul_counter             int(10)                       null,
    max_next_overhaul_counter             int(10)                       null,
    min_next_overhaul_date                date                          null,
    max_next_overhaul_date                date                          null,
    effective_hours_at_work_start         int(10)                       null,
    theoretical_overhaul_counter          int(10)                       null,
    intelligent_overhaul_counter          int(10)                       null,
    intelligent_min_next_overhaul_counter int(10)                       null,
    intelligent_max_next_overhaul_counter int(10)                       null,
    effective_date_at_work_end            date                          null,
    theoretical_overhaul_date             date                          null,
    intelligent_overhaul_date             date                          null,
    intelligent_min_next_overhaul_date    date                          null,
    intelligent_max_next_overhaul_date    date                          null,
    overlapping_group_id                  int unsigned                  null,
    is_visit_rg                           tinyint(1) unsigned default 0 not null
);

create table maintenance_view
(
    id                                                int unsigned default 0 not null
        primary key,
    frame_total_time                                  int(10)                null,
    frame_time_since_rg                               int(10)                null,
    engine_time_since_rg                              int(10)                null,
    propeller_time_since_rg                           int(10)                null,
    potential_remaining_next_time_action              int(10)                null,
    potential_remaining_next_time_visit               int(10)                null,
    potential_remaining_next_priority_time_action     int(10)                null,
    potential_remaining_next_calendar_action          int(10)                null,
    potential_remaining_next_calendar_visit           int(10)                null,
    potential_remaining_next_priority_calendar_action int(10)                null,
    next_time_action_id                               int unsigned           null,
    next_time_visit_id                                int unsigned           null,
    next_priority_time_action_id                      int unsigned           null,
    next_calendar_action_id                           int unsigned           null,
    next_calendar_visit_id                            int unsigned           null,
    next_priority_calendar_action_id                  int unsigned           null,
    overhaul_counter_action                           int(10)                null,
    overhaul_counter_visit                            int(10)                null,
    overhaul_date_action                              date                   null,
    overhaul_date_visit                               date                   null
);

create table map_logger_monitoring
(
    record_id  int unsigned not null
        primary key,
    count_data int unsigned not null
)
    comment 'link between flight and track';

create table map_track
(
    id            int unsigned auto_increment
        primary key,
    record_id     int unsigned                  null,
    start_data    int unsigned                  null,
    number        int unsigned                  null,
    start_time    datetime                      null,
    latitude_max  double                        null,
    longitude_max double                        null,
    latitude_min  double                        null,
    longitude_min double                        null,
    visible       tinyint(1) unsigned default 1 not null
)
    comment 'Tracks split from GDR';

create table nationality
(
    code  char(2) default '' not null
        primary key,
    label varchar(255)       not null
);

create table parameter
(
    code       varchar(255)                  not null,
    key_id     int unsigned        default 0 not null,
    enabled    tinyint(1) unsigned default 0 null,
    int_value  int unsigned        default 0 null,
    char_value text                          null,
    primary key (code, key_id)
);

create table payment_summary_file
(
    id              int unsigned auto_increment
        primary key,
    file_type       varchar(255)            null,
    content         longblob                null,
    record_date     datetime                null,
    total_entry     int unsigned            null,
    total_amount    decimal(15, 5) unsigned null,
    payment_type_id int unsigned            null
);

CREATE TABLE `payment_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` text,
  `text_field_label` text,
  `only_admin` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `pos_key_id` int(10) unsigned DEFAULT NULL,
  `treasury_account_id` int(10) unsigned DEFAULT NULL,
  `person_delivery` tinyint(1) unsigned DEFAULT '0',
  `person_budget_id` int(10) unsigned DEFAULT NULL,
  `treasury_budget_id` int(10) unsigned DEFAULT NULL,
  `minimum_amount` decimal(10,0) DEFAULT NULL,
  `maximum_amount` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) COMMENT='type of payment description';

create table person
(
    id                     int unsigned auto_increment
        primary key,
    name                   varchar(255)                                      not null,
    hash_password          varchar(255)                                      not null,
    first_name             varchar(255)                                      null,
    last_name              varchar(255)                                      null,
    profile                bigint unsigned     default 0                     not null,
    view_type              int unsigned                                      null,
    view_width             tinyint unsigned    default 12                    not null,
    view_height            tinyint(4) unsigned default 30                    not null,
    aircrafts_viewed       varchar(255)                                      null,
    inst_viewed            varchar(255)                                      null,
    email                  varchar(255)                                      null,
    timezone               varchar(255)                                      null,
    address                varchar(255)                                      null,
    zipcode                varchar(255)                                      null,
    city                   varchar(255)                                      null,
    state                  varchar(255)                                      null,
    country                varchar(255)                                      null,
    home_phone             varchar(255)                                      null,
    work_phone             varchar(255)                                      null,
    cell_phone             varchar(255)                                      null,
    lang                   varchar(255)                                      null,
    notification           tinyint unsigned                                  null,
    activated              tinyint(1) unsigned default 1                     null,
    birthdate              datetime            default '0000-00-00 00:00:00' null,
    sex                    tinyint(1) unsigned default 0                     not null,
    nationality            char(2)                                           null,
    total_flight_time      int unsigned        default 0                     null,
    date_total_flight_time datetime                                          null,
    guid                   varchar(255)                                      null,
    activity_notification  bigint unsigned                                   null,
    constraint idx_name
        unique (name),
    constraint name_3
        unique (name)
);

create table person_awaiting_activation
(
    id              int unsigned auto_increment
        primary key,
    login           varchar(255)        not null,
    ip              varchar(255)        not null,
    code            varchar(255)        not null,
    category        varchar(255)        not null,
    expiration_date datetime            null,
    used            tinyint(1) unsigned null
)
    comment 'member_awaiting_activation';

create table product
(
    id          int unsigned auto_increment
        primary key,
    label       varchar(255)                  not null,
    unit        varchar(255)                  not null,
    free_sale   tinyint(1)          default 0 not null,
    locked      tinyint(1) unsigned default 0 null,
    variable_id int unsigned                  null,
    sale_type   tinyint(1) unsigned           null
);

create table profile
(
    id                bigint unsigned default 0 not null
        primary key,
    name              varchar(255)              null,
    permits           int unsigned              null,
    permits2          int unsigned    default 0 not null,
    permits3          int unsigned    default 0 not null,
    pictogram         int unsigned              null,
    default_status_id int unsigned              null
);

create table profile_accounting_notification
(
    profile_id    bigint unsigned default 0 not null,
    accounting_id int unsigned    default 0 not null,
    primary key (profile_id, accounting_id)
);

create table profile_extra_field_join
(
    profile_id        bigint unsigned default 0 not null,
    business_field_id int unsigned    default 0 not null,
    primary key (profile_id, business_field_id)
);

create table profile_profile_view
(
    profile_id          bigint unsigned not null,
    viewable_profile_id bigint unsigned not null,
    primary key (profile_id, viewable_profile_id)
);

create table profile_required_account_type
(
    profile_id      bigint unsigned default 0 not null,
    account_type_id int unsigned    default 0 not null,
    primary key (profile_id, account_type_id)
);

create table profile_resource_type_place
(
    profile_id       bigint unsigned default 0 not null,
    resource_type_id int unsigned    default 0 not null,
    place_num        int unsigned              not null,
    primary key (profile_id, resource_type_id, place_num)
);

create table profile_resource_type_view
(
    profile_id                bigint unsigned not null,
    viewable_resource_type_id int unsigned    not null,
    primary key (profile_id, viewable_resource_type_id)
);

create table profile_validity_type_join
(
    profile_id        bigint unsigned default 0 not null,
    validity_type_id  int unsigned    default 0 not null,
    manage4oneself    int(1)          default 0 not null,
    certify           int(1)          default 0 not null,
    optional_contract tinyint(1)      default 0 not null,
    primary key (profile_id, validity_type_id)
);

create table profile_validity_type_notification
(
    profile_id       bigint unsigned default 0 not null,
    validity_type_id int unsigned    default 0 not null,
    primary key (profile_id, validity_type_id)
);

create table psp_return
(
    id                 int unsigned auto_increment
        primary key,
    psp_transaction_id int unsigned null,
    bank_answer        text         null,
    bank_misc          text         null
);

create table psp_transaction
(
    id                int unsigned auto_increment
        primary key,
    credit_account_id int unsigned           not null,
    debit_account_id  int unsigned           not null,
    transaction_date  datetime               not null,
    amount            float                  not null,
    description       varchar(255)           null,
    payment_type_id   int unsigned           not null,
    state             int unsigned default 0 not null,
    token             text                   null
);

create table psp_transaction_account_entry
(
    psp_transaction_id    int unsigned default 0 not null,
    account_entry_flow_id int unsigned default 0 not null,
    primary key (psp_transaction_id, account_entry_flow_id)
);

create table regular_presence_inst_date
(
    id         int unsigned auto_increment
        primary key,
    person_id  int unsigned     null,
    start_day  tinyint unsigned null,
    end_day    tinyint unsigned null,
    start_hour time             null,
    end_hour   time             null
);

create table resource
(
    id               int unsigned auto_increment
        primary key,
    name             varchar(255)                  null,
    resource_type_id int unsigned                  null,
    comments         varchar(255)                  null,
    order_num        int                           null,
    activated        tinyint(1) unsigned default 1 not null,
    bookable         int unsigned        default 1 null,
    physical         int unsigned        default 1 null,
    color            int unsigned                  null
)
    comment 'List of resource';

create table resource_exceptional_availability
(
    id          int unsigned auto_increment
        primary key,
    resource_id int unsigned        null,
    start_date  datetime            null,
    end_date    datetime            null,
    presence    tinyint(1) unsigned null
);

create table resource_regular_availability
(
    id          int unsigned auto_increment
        primary key,
    resource_id int unsigned        null,
    start_day   tinyint(1) unsigned null,
    end_day     tinyint(1) unsigned null,
    start_hour  time                null,
    end_hour    time                null
);

create table resource_type
(
    id                   int unsigned auto_increment
        primary key,
    name                 varchar(255)                   null,
    category             int unsigned                   null,
    seats_available      int                 default -1 null,
    comments             varchar(255)                   null,
    order_num            int                            null,
    activated            tinyint(1) unsigned default 1  not null,
    max_booking_duration int(10)             default -1 null,
    pictogram            int unsigned                   null
)
    comment 'List of resource type';

create table resource_type_place_tag
(
    resource_type_id int unsigned default 0  not null,
    place_num        tinyint(1) unsigned     not null,
    place_tag        varchar(255)            null,
    place_quantity   int(10)      default -1 null,
    primary key (resource_type_id, place_num)
);

create table sale_2_stock
(
    id                           int unsigned auto_increment
        primary key,
    product_id                   int unsigned    null,
    stock_id                     int unsigned    not null,
    stock_variation_qty_per_sale float default 0 not null
);

create table sale_2_validity_type
(
    id               int unsigned auto_increment
        primary key,
    validity_type_id int unsigned not null,
    new_formula      varchar(255) null,
    update_formula   varchar(255) null,
    product_id       int unsigned null,
    constraint validity_type_id
        unique (validity_type_id)
)
    comment 'List of validity type into sale';

create index idx_product
    on sale_2_validity_type (product_id);

create table sale_pricing
(
    id                  int unsigned auto_increment
        primary key,
    price_formula       text                          null,
    debit_account_id    int unsigned                  null,
    credit_account_id   int unsigned                  null,
    debit_account_type  tinyint(1) unsigned default 0 null,
    credit_account_type tinyint(1) unsigned default 0 null,
    credit_budget_id    int unsigned                  null,
    debit_budget_id     int unsigned                  null,
    order_num           int                           null,
    label               varchar(255)                  null,
    variable_formula    varchar(255)                  null,
    debit_bill_num      int unsigned                  null,
    credit_bill_num     int unsigned                  null,
    qty_formula         text                          null,
    unit_price_formula  text                          null,
    added_product_id    int unsigned                  null
);

create table sale_pricing_product
(
    sale_pricing_id int unsigned default 0 not null,
    product_id      int unsigned default 0 not null,
    primary key (sale_pricing_id, product_id)
);

create table sale_pricing_profile
(
    sale_pricing_id int unsigned              not null,
    profile_id      bigint unsigned default 0 not null,
    primary key (sale_pricing_id, profile_id)
);

create table sale_trigger
(
    id             int(10) auto_increment
        primary key,
    name           varchar(255)                  null,
    event          varchar(255)                  null,
    locked         tinyint(1) unsigned default 0 not null,
    query          text                          null,
    validate_entry tinyint(1)          default 0 null,
    group_sales    tinyint(1)          default 0 null
)
    comment 'List of sale trigger';

create table spreadsheet_parameter
(
    file_number       int(10)      not null,
    spreadsheet_key   varchar(255) not null,
    spreadsheet_value varchar(255) not null,
    file_name         varchar(255) null,
    google_url        varchar(255) not null,
    file_type         int unsigned null,
    primary key (file_number, spreadsheet_key, spreadsheet_value)
);

create table statistic
(
    name       varchar(255)                not null,
    of_version tinyint(3)     default 0    not null,
    value      decimal(15, 2) default 0.00 not null,
    primary key (name, of_version)
);

create table status
(
    id        int unsigned auto_increment
        primary key,
    abbrev    varchar(255) null,
    name      varchar(255) null,
    pictogram int unsigned null
)
    comment 'List of functions for pilot';

create table stock
(
    id            int unsigned auto_increment
        primary key,
    label         varchar(255) not null,
    stock_type_id int unsigned not null
);

create table stock_level
(
    id         int unsigned auto_increment
        primary key,
    stock_id   int unsigned    not null,
    qty        float default 0 not null,
    stock_date datetime        null
);

create table stock_type
(
    id    int unsigned auto_increment
        primary key,
    label varchar(255) not null,
    unit  varchar(255) not null
);

create table stock_variation
(
    id             int unsigned auto_increment
        primary key,
    stock_id       int unsigned                  not null,
    qty            float               default 0 not null,
    variation_date datetime                      null,
    validated      tinyint(1) unsigned default 0 null
);

create table stock_variation_account_entry
(
    product_id            int unsigned default 0 not null,
    stock_variation_id    int unsigned           not null,
    account_entry_flow_id int unsigned           not null,
    person_id             int unsigned           not null,
    primary key (product_id, stock_variation_id, account_entry_flow_id)
);

create index idx_account_entry_flow_id
    on stock_variation_account_entry (account_entry_flow_id);

create table structure
(
    id                            int(10)         default 0 not null
        primary key,
    name                          varchar(255)              null,
    info_cell                     text                      null,
    logo                          longblob                  null,
    logo_name                     varchar(255)              null,
    logo_ext                      varchar(25)               null,
    logo_size                     int                       null,
    first_hour_displayed          time                      null,
    last_hour_displayed           time                      null,
    usual_profiles                bigint unsigned default 0 not null,
    icao                          varchar(6)                null,
    default_slot_range            int unsigned              null,
    min_slot_range                tinyint unsigned          null,
    twilight_range                tinyint unsigned          null,
    mailing_list_name             varchar(255)              null,
    mailing_list_type             varchar(255)              null,
    structure_site_url            varchar(255)              null,
    default_timezone              varchar(255)              not null,
    lang                          varchar(255)              not null,
    admin_num                     int unsigned              not null,
    default_view_type             int unsigned              null,
    address                       varchar(255)              null,
    zipcode                       varchar(255)              null,
    city                          varchar(255)              null,
    state                         varchar(255)              null,
    country                       varchar(255)              null,
    phone                         varchar(255)              null,
    fax                           varchar(255)              null,
    email                         varchar(255)              null,
    default_notification          int(3) unsigned           null,
    welcome_cell                  text                      null,
    business                      text                      null,
    default_activity_notification bigint unsigned           null,
    siren                         int(10)                   null
);

create table supplier_bill
(
    id                    int unsigned auto_increment
        primary key,
    supplier_bill_type_id int unsigned         not null,
    bill_date             datetime             null,
    description           varchar(255)         null,
    ordinal               int unsigned         not null,
    validated             tinyint(1) default 0 null,
    supplier_bill_file_id int(10)              null
);

create table supplier_bill_account_entry
(
    supplier_bill_id      int unsigned not null,
    account_entry_flow_id int unsigned not null,
    primary key (supplier_bill_id, account_entry_flow_id)
);

create index idx_account_entry_flow_id
    on supplier_bill_account_entry (account_entry_flow_id);

create table supplier_bill_email_parsed
(
    id                    int(10) auto_increment
        primary key,
    uid                   int(10)      null,
    email_address         varchar(255) null,
    supplier_bill_file_id int(10)      null
);

create table supplier_bill_file
(
    id        int unsigned auto_increment
        primary key,
    file_type varchar(255) null,
    content   longblob     null,
    name      varchar(255) not null
);

create table supplier_bill_type
(
    id                  int unsigned auto_increment
        primary key,
    supplier_account_id int unsigned not null,
    name                varchar(255) null,
    account_id          int unsigned not null,
    vat_account_id      int unsigned null,
    supplier_budget_id  int unsigned null,
    account_budget_id   int unsigned null,
    vat_budget_id       int unsigned null,
    rule                text         null,
    sender_email        varchar(255) null,
    subject             varchar(255) null
);

create table table_trigger
(
    id              int unsigned auto_increment
        primary key,
    monitored_table varchar(255) not null,
    trigger_formula varchar(255) not null
);

create table tank
(
    id                 int unsigned auto_increment
        primary key,
    aircraft_type_id   int unsigned                 not null,
    tank_type_id       int unsigned                 not null,
    unit_id            int unsigned                 not null,
    label              varchar(255)                 not null,
    max_quantity       decimal(15, 2) default -1.00 not null,
    unlimited_quantity int unsigned   default 1     null
);

create table track_record
(
    id                     int unsigned auto_increment
        primary key,
    in_progress            tinyint(1) unsigned                    null,
    start_date             datetime default '0000-00-00 00:00:00' null,
    activated              tinyint(1) unsigned                    null,
    datechsys_track_id     int unsigned                           null,
    resource_cat           int unsigned                           not null,
    resource_id            int unsigned                           not null,
    additional_information int unsigned                           null
)
    comment 'List of flight record';

create table track_record_data
(
    id            int unsigned auto_increment
        primary key,
    record_id     int unsigned        not null,
    record_date   datetime            null,
    longitude     double              null,
    latitude      double              null,
    altitude      int(7)              null,
    speed         double              null,
    track         int(3) unsigned     null,
    acc_x         int(6)              null,
    acc_y         int(6)              null,
    acc_z         int(6)              null,
    pressure      int(5) unsigned     null,
    battery_level int(4) unsigned     null,
    gps_fix       tinyint(1) unsigned null,
    receive_date  datetime            null
)
    comment 'List of flight record data';

create index idx_record_id
    on track_record_data (record_id);

create table track_resource
(
    id   int unsigned auto_increment
        primary key,
    name varchar(255) not null
);

CREATE TABLE `training` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `training_activity` (
  `activity_id` int(10) unsigned NOT NULL,
  `training_item_id` int(10) unsigned NOT NULL DEFAULT '0',
  `training_program_id` int(10) unsigned NOT NULL DEFAULT '1',
  `level_reached` int(10) unsigned DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `validated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`activity_id`,`training_item_id`,`training_program_id`),
  KEY `training_program_id_idx` (`training_program_id`),
  KEY `training_program_item_id_idx` (`training_program_id`,`training_item_id`),
  CONSTRAINT `training_activity_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `training_activity_training_program_item_id_fk` FOREIGN KEY (`training_program_id`, `training_item_id`) REFERENCES `training_program_item` (`training_program_id`, `training_item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE `training_activity_next_item` (
  `student_id` int(10) unsigned NOT NULL,
  `training_program_id` int(10) unsigned NOT NULL,
  `training_item_id` int(10) unsigned NOT NULL,
  `reporting_offset` tinyint(10) unsigned NOT NULL DEFAULT '0',
  `item_preselected` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`student_id`,`training_program_id`,`training_item_id`),
  KEY `training_item_id_idx` (`training_item_id`),
  KEY `training_program_id_idx` (`training_program_id`),
  KEY `training_activity_next_item_training_program_item_id_fk` (`training_program_id`,`training_item_id`),
  CONSTRAINT `training_activity_next_item_student_id_fk` FOREIGN KEY (`student_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `training_activity_next_item_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `training_activity_next_item_training_program_item_id_fk` FOREIGN KEY (`training_program_id`, `training_item_id`) REFERENCES `training_program_item` (`training_program_id`, `training_item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE `training_activity_person` (
  `activity_id` int(10) unsigned NOT NULL,
  `person_id` int(10) unsigned NOT NULL,
  `num` int(10) unsigned NOT NULL DEFAULT '0',
  `training_checking_date` datetime DEFAULT NULL,
  `training_checking_sentence` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`activity_id`,`person_id`,`num`),
  KEY `person_id_idx` (`person_id`),
  CONSTRAINT `training_activity_person_activity_id_fk` FOREIGN KEY (`activity_id`) REFERENCES `flight` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `training_activity_person_person_id_fk` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE `training_phase` (
  `training_program_item_order_num` int(11) unsigned NOT NULL,
  `training_program_id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`training_program_item_order_num`,`training_program_id`),
  KEY `training_program_id_idx` (`training_program_id`),
  CONSTRAINT `training_phase_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE `training_program` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`training_id` int(10) unsigned DEFAULT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `training_id_idx` (`training_id`),
CONSTRAINT `training_program_training_id_fk` FOREIGN KEY (`training_id`) REFERENCES `training` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE `training_program_activity` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `training_program_id` int(10) unsigned NOT NULL,
  `activity_type_id` bigint(20) unsigned NOT NULL,
  `duration` int(11) unsigned DEFAULT NULL,
  `breaktime` int(11) unsigned DEFAULT NULL,
  `order_num` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `training_program_id_idx` (`training_program_id`),
  KEY `activity_type_id_idx` (`activity_type_id`),
  CONSTRAINT `training_program_activity_activity_type_id_fk` FOREIGN KEY (`activity_type_id`) REFERENCES `activity_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `training_program_activity_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE `training_item` (
  `training_id` int(10) unsigned NOT NULL,
  `training_item_label_id` int(10) unsigned NOT NULL,
  `number_of_sessions` int(11) NOT NULL,
  `order_num` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`training_id`,`training_item_label_id`),
  KEY `order_num_idx` (`order_num` ASC),
  CONSTRAINT `training_item_training_id_fk` FOREIGN KEY (`training_id`) REFERENCES `training` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

create table uncomp_flight_type
(
    id1 int unsigned null,
    id2 int unsigned null
)
    comment 'List of sales';

create table validity_type
(
    id                       int unsigned auto_increment
        primary key,
    name                     varchar(255)                   not null,
    time_limitation          tinyint(1)          default 0  not null,
    ident_value_enable       tinyint(1)          default 0  not null,
    grant_date_enable        tinyint(1)          default 0  not null,
    mandatory                tinyint(1)          default 0  not null,
    experience_formula       text                           null,
    mandatory_access_control tinyint(1) unsigned default 0  null,
    alert_on_login           int(10)             default -2 null,
    first_reminder_alert     int unsigned        default 0  null,
    reminder_frequency_alert int unsigned        default 0  null,
    associate_attachment     tinyint(1) unsigned default 0  not null,
    certification_process    tinyint(1)          default 0  not null,
    is_contract              tinyint(1)          default 0  not null,
    contract_filename        varchar(255)                   not null,
    contract_file            mediumblob                     not null,
    contract_file_extension  varchar(4)                     not null,
    is_OF_contract           tinyint(1)          default 0  not null,
    activated                tinyint(1) unsigned default 1  not null
);

create table validity
(
    id                  int unsigned auto_increment
        primary key,
    person_id           int unsigned         not null,
    validity_type_id    int unsigned         not null,
    registration_date   datetime             not null,
    expire_date         date                 null,
    no_alert            tinyint(1) default 0 not null,
    ident_value         varchar(255)         null,
    grant_date          date                 null,
    checker_person_id   int unsigned         not null,
    checking_date       date                 not null,
    checking_sentence   varchar(255)         not null,
    is_current_validity tinyint(1) default 0 not null,
    constraint validity_person_id_validity_type_id_registration_date_uindex
        unique (person_id, validity_type_id, registration_date),
    constraint validity_person_id_fk
        foreign key (person_id) references person (id),
    constraint validity_validity_type_id_fk
        foreign key (validity_type_id) references validity_type (id)
);

create table validity_type_page
(
    id               int unsigned auto_increment
        primary key,
    validity_type_id int unsigned        not null,
    page_index       int unsigned        not null,
    label            varchar(40)         not null,
    is_mandatory     tinyint(1) unsigned not null,
    constraint validity_type_page_validity_type_id_page_index_uindex
        unique (validity_type_id, page_index),
    constraint validity_type_page_validity_type_id_fk
        foreign key (validity_type_id) references validity_type (id)
);

create table validity_page
(
    id                    int unsigned auto_increment
        primary key,
    validity_type_page_id int unsigned not null,
    filename              varchar(255) not null,
    file                  mediumblob   not null,
    file_extension        varchar(4)   not null,
    constraint validity_page_validity_type_page_id_fk
        foreign key (validity_type_page_id) references validity_type_page (id)
);

create table validity_2_validity_page
(
    validity_id      int unsigned not null,
    validity_page_id int unsigned not null,
    primary key (validity_id, validity_page_id),
    constraint validity_2_validity_page_validity_id_fk
        foreign key (validity_id) references validity (id),
    constraint validity_2_validity_page_validity_page_id_fk
        foreign key (validity_page_id) references validity_page (id)
);

create table variable
(
    id         int auto_increment
        primary key,
    variable   varchar(255) not null,
    label      varchar(255) not null,
    category   tinyint(1)   null,
    value_type varchar(255) null,
    order_num  int          not null
);

create table variable_value
(
    id           int auto_increment
        primary key,
    variable_id  int            not null,
    assign_value decimal(15, 2) not null,
    start_date   datetime       not null
);

create table web_feed
(
    id               int unsigned auto_increment
        primary key,
    channel_id       int unsigned not null,
    publication_date datetime     not null,
    title            varchar(255) null,
    content          text         null
);

create table web_feed_channel
(
    id    int unsigned auto_increment
        primary key,
    label varchar(255) not null
);

create definer = overallCustomer@localhost view eligible_pilot_for_first_flight as
select `of40_aeroclublys`.`person`.`id` AS `person_id`
from `of40_aeroclublys`.`person`
where ((`of40_aeroclublys`.`person`.`activated` = 1) and
       ((`of40_aeroclublys`.`person`.`profile` & (select `of40_aeroclublys`.`parameter`.`int_value`
                                                  from `of40_aeroclublys`.`parameter`
                                                  where (`of40_aeroclublys`.`parameter`.`code` =
                                                         'EXTERNAL_BOOKING_PILOT_PROFILE'))) > 0) and
       `of40_aeroclublys`.`person`.`id` in (select `of40_aeroclublys`.`regular_presence_inst_date`.`person_id`
                                            from `of40_aeroclublys`.`regular_presence_inst_date`
                                            union
                                            select `of40_aeroclublys`.`exceptionnal_inst_date`.`person_id`
                                            from `of40_aeroclublys`.`exceptionnal_inst_date`
                                            where ((`of40_aeroclublys`.`exceptionnal_inst_date`.`end_date` > now()) and
                                                   (`of40_aeroclublys`.`exceptionnal_inst_date`.`presence` = 1))));

create definer = overallCustomer@localhost view eligible_resource_for_first_flight as
select `of40_aeroclublys`.`resource`.`id` AS `resource_id`
from ((`of40_aeroclublys`.`resource` join `of40_aeroclublys`.`business_field_content` on ((
        `of40_aeroclublys`.`business_field_content`.`category_id` = `of40_aeroclublys`.`resource`.`id`)))
         join `of40_aeroclublys`.`business_field` on ((`of40_aeroclublys`.`business_field`.`id` =
                                                       `of40_aeroclublys`.`business_field_content`.`business_field_id`)))
where ((`of40_aeroclublys`.`resource`.`activated` = 1) and
       (`of40_aeroclublys`.`business_field`.`variable` = 'canResourcePerformFirstFlights') and
       (`of40_aeroclublys`.`business_field_content`.`content` = '1') and
       (`of40_aeroclublys`.`resource`.`bookable` = 1) and ((select count(0)
                                                            from `of40_aeroclublys`.`resource_type_place_tag` `seat`
                                                            where (`seat`.`resource_type_id` =
                                                                   `of40_aeroclublys`.`resource`.`resource_type_id`)) >=
                                                           2) and
       `of40_aeroclublys`.`resource`.`id` in (select `of40_aeroclublys`.`resource_regular_availability`.`resource_id`
                                              from `of40_aeroclublys`.`resource_regular_availability`
                                              union
                                              select `of40_aeroclublys`.`resource_exceptional_availability`.`resource_id`
                                              from `of40_aeroclublys`.`resource_exceptional_availability`
                                              where ((`of40_aeroclublys`.`resource_exceptional_availability`.`end_date` >
                                                      now()) and
                                                     (`of40_aeroclublys`.`resource_exceptional_availability`.`presence` = 1))));

create
    definer = root@localhost function distanceBetween2Point(latitude1 double, longitude1 double, latitude2 double,
                                                            longitude2 double) returns double
BEGIN
    DECLARE rlongitude1 DOUBLE;
    DECLARE rlatitude1 DOUBLE;
    DECLARE rlongitude2 DOUBLE;
    DECLARE rlatitude2 DOUBLE;
    DECLARE dlongitude DOUBLE;
    DECLARE dlatitude DOUBLE;
    DECLARE a DOUBLE;

    SET rlongitude1 = RADIANS(longitude1);
    SET rlatitude1 = RADIANS(latitude1);
    SET rlongitude2 = RADIANS(longitude2);
    SET rlatitude2 = RADIANS(latitude2);
    SET dlongitude = (rlongitude2 - rlongitude1) / 2;
    SET dlatitude = (rlatitude2 - rlatitude1) / 2;
    SET a = SIN(dlatitude) * SIN(dlatitude) + COS(rlatitude1) * COS(rlatitude2) * SIN(dlongitude) * SIN(dlongitude);
    RETURN (6378137 * 2 * ATAN2(SQRT(a), SQRT(1 - a)));
END;

create
    definer = overallCustomer@localhost function formatDecimal(string varchar(255)) returns varchar(255)
BEGIN
    DECLARE replacedString VARCHAR(255);
    SET replacedString = string;
    SET @decimalSeparator = (SELECT char_value FROM parameter WHERE code = 'DECIMAL_SEPARATOR' AND key_id = 0 LIMIT 1);

    SELECT IF(replacedString <> '0.00' AND replacedString <> '0', TRIM(TRAILING '0' FROM replacedString),
              replacedString)
    INTO replacedString;
    SELECT TRIM(TRAILING '.' FROM replacedString) INTO replacedString;
    SELECT REPLACE(replacedString, '.', IFNULL(@decimalSeparator, '.')) INTO replacedString;
    RETURN replacedString;
END;

create
    definer = root@localhost function isBalancedAccountEntryFlow(flowId int) returns int
BEGIN
    DECLARE isBalanced INT;

    SELECT IF(SUM(debit) <> SUM(credit), 0, 1)
    into isBalanced
    FROM account_entry
    WHERE flow_id = flowId
    GROUP BY flow_id;

    RETURN isBalanced;
END;

create
    definer = root@localhost function nearestPoint(latRef double, longRef double, distanceMax int) returns varchar(6)
BEGIN
    DECLARE rlongitude1 DOUBLE;
    DECLARE rlatitude1 DOUBLE;
    DECLARE rlongitude2 DOUBLE;
    DECLARE rlatitude2 DOUBLE;
    DECLARE a DOUBLE;
    DECLARE icao_name VARCHAR(6);
    DECLARE lenght DOUBLE;

    SET a = 2 * 6378137 * pi() * distanceMax / (1852 * 60 * 360);
    SET rlongitude1 = longRef + a / cos(latRef);
    SET rlongitude2 = longRef - a / cos(latRef);
    SET rlatitude1 = latRef + a;
    SET rlatitude2 = latRef - a;

    SELECT loc1.icao_name,
           distanceBetween2Point(
                   (SELECT loc2.latitude FROM location AS loc2 WHERE loc2.icao_name = loc1.icao_name),
                   (SELECT loc3.longitude FROM location AS loc3 WHERE loc3.icao_name = loc1.icao_name),
                   latRef,
                   longRef) AS distance
    FROM location AS loc1
    WHERE IF(rlongitude2 < rlongitude1,
             loc1.longitude BETWEEN rlongitude2 AND rlongitude1,
             loc1.longitude BETWEEN rlongitude1 AND rlongitude2)
      AND IF(rlatitude2 < rlatitude1,
             loc1.latitude BETWEEN rlatitude2 AND rlatitude1,
             loc1.latitude BETWEEN rlatitude1 AND rlatitude2)
    HAVING distance < distanceMax
    ORDER BY distance
    LIMIT 1
    INTO icao_name, lenght;

    RETURN icao_name;
END;

create
    definer = overallCustomer@localhost function sexa2HoursHundredths(sexacentimal int) returns varchar(255)
BEGIN
    DECLARE convertedValue VARCHAR(255);
    DECLARE hours VARCHAR(255);
    DECLARE roundedHours VARCHAR(255);
    DECLARE remainingTime VARCHAR(255);
    DECLARE decimalSeparator VARCHAR(1);
    SET hours = sexacentimal / 600;
    SET roundedHours = IF(sexacentimal > 0, FLOOR(hours), CEIL(hours));
    SET remainingTime = ABS(sexacentimal - roundedHours * 600);
    SET decimalSeparator =
            (SELECT IFNULL(char_value, '.') FROM parameter WHERE code = 'DECIMAL_SEPARATOR' AND key_id = 0 LIMIT 1);

    SELECT CONCAT(
                   roundedHours, decimalSeparator, LPAD(FLOOR(remainingTime / 6), 2, '0')
               )
    INTO convertedValue;

    RETURN convertedValue;
END;

create
    definer = overallCustomer@localhost function sexa2HoursMinute(sexacentimal int) returns varchar(255)
BEGIN
    DECLARE convertedValue VARCHAR(255);
    DECLARE hours VARCHAR(255); # We separate hours computation from roundHours in order to keep the minus sign if hours is 0 but sexacentimal negative
    DECLARE roundedHours VARCHAR(255);
    DECLARE remainingTime VARCHAR(255);
    SET hours = sexacentimal / 600;
    SET roundedHours = IF(sexacentimal > 0, FLOOR(hours), CEIL(hours));
    SET remainingTime = ABS(sexacentimal - roundedHours * 600);

    SELECT CONCAT(
                   roundedHours, ':', LPAD(FLOOR(remainingTime / 10), 2, '0')
               )
    INTO convertedValue;

    RETURN convertedValue;
END;

create
    definer = root@localhost function stripChars(word varchar(255)) returns varchar(255)
BEGIN
    DECLARE stripWord VARCHAR(255);
    SET stripWord = word;
    SELECT IF(stripWord REGEXP '[-]', REPLACE(stripWord, '-', ''), stripWord) INTO stripWord;
    SELECT IF(stripWord REGEXP '[ ]', REPLACE(stripWord, ' ', ''), stripWord) INTO stripWord;
    RETURN stripWord;
END;

create
    definer = overallCustomer@localhost function sumAccountEntry(accountId int, endDate datetime) returns decimal(15, 2)
BEGIN
    DECLARE balanceDate DATETIME;
    DECLARE balanceDebit DECIMAL(15, 2);
    DECLARE balanceCredit DECIMAL(15, 2);
    DECLARE totalDebit DECIMAL(15, 2);
    DECLARE totalCredit DECIMAL(15, 2);
    DECLARE totalBalance DECIMAL(15, 2);


    SELECT balance_date, IFNULL(debit, 0.00), IFNULL(credit, 0.00)
    INTO balanceDate, balanceDebit, balanceCredit
    FROM balance_date
             LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId)
    WHERE balance_date.balance_date < endDate
    ORDER BY balance_date DESC
    LIMIT 1;


    IF (SELECT FOUND_ROWS()) = 0 THEN
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit, balanceCredit;
    END IF;

    SELECT IFNULL(SUM(debit), 0.00) + balanceDebit, IFNULL(SUM(credit), 0.00) + balanceCredit
    INTO totalDebit, totalCredit
    FROM account_entry
    WHERE account_id = accountId
      AND account_date >= CAST(balanceDate AS DATETIME)
      AND account_date < CAST(endDate AS DATETIME);

    SELECT (totalCredit - totalDebit) INTO totalBalance;

    RETURN totalBalance;
END;

create
    definer = overallCustomer@localhost function sumAccountEntryCredit(accountId int, endDate datetime) returns decimal(15, 2)
BEGIN
    DECLARE balanceDate DATETIME;
    DECLARE balanceCredit DECIMAL(15, 2);
    DECLARE totalCredit DECIMAL(15, 2);


    SELECT balance_date, IFNULL(credit, 0.00)
    INTO balanceDate, balanceCredit
    FROM balance_date
             LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId)
    WHERE balance_date.balance_date < endDate
    ORDER BY balance_date DESC
    LIMIT 1;


    IF (SELECT FOUND_ROWS()) = 0 THEN
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceCredit;
    END IF;

    SELECT IFNULL(SUM(credit), 0.00) + balanceCredit
    INTO totalCredit
    FROM account_entry
    WHERE account_id = accountId
      AND account_date >= CAST(balanceDate AS DATETIME)
      AND account_date < CAST(endDate AS DATETIME);

    RETURN totalCredit;
END;

create
    definer = overallCustomer@localhost function sumAccountEntryDebit(accountId int, endDate datetime) returns decimal(15, 2)
BEGIN
    DECLARE balanceDate DATETIME;
    DECLARE balanceDebit DECIMAL(15, 2);
    DECLARE totalDebit DECIMAL(15, 2);


    SELECT balance_date, IFNULL(debit, 0.00)
    INTO balanceDate, balanceDebit
    FROM balance_date
             LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId)
    WHERE balance_date.balance_date < endDate
    ORDER BY balance_date DESC
    LIMIT 1;


    IF (SELECT FOUND_ROWS()) = 0 THEN
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit;
    END IF;

    SELECT IFNULL(SUM(debit), 0.00) + balanceDebit
    INTO totalDebit
    FROM account_entry
    WHERE account_id = accountId
      AND account_date >= CAST(balanceDate AS DATETIME)
      AND account_date < CAST(endDate AS DATETIME);

    RETURN totalDebit;
END;

create
    definer = root@localhost function sumValidatedAccountEntry(accountId int, endDate datetime) returns decimal(15, 2)
BEGIN
    DECLARE balanceDate DATETIME;
    DECLARE balanceDebit DECIMAL(15, 2);
    DECLARE balanceCredit DECIMAL(15, 2);
    DECLARE totalDebit DECIMAL(15, 2);
    DECLARE totalCredit DECIMAL(15, 2);
    DECLARE totalBalance DECIMAL(15, 2);


    SELECT balance_date, IFNULL(debit, 0.00), IFNULL(credit, 0.00)
    INTO balanceDate, balanceDebit, balanceCredit
    FROM balance_date
             LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId)
    WHERE balance_date.balance_date < endDate
    ORDER BY balance_date DESC
    LIMIT 1;


    IF (SELECT FOUND_ROWS()) = 0 THEN
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit, balanceCredit;
    END IF;

    SELECT IFNULL(SUM(debit), 0.00) + balanceDebit, IFNULL(SUM(credit), 0.00) + balanceCredit
    INTO totalDebit, totalCredit
    FROM account_entry
    WHERE account_id = accountId
      AND account_date >= CAST(balanceDate AS DATETIME)
      AND account_date < CAST(endDate AS DATETIME)
      AND validated = 1;

    SELECT (totalCredit - totalDebit) INTO totalBalance;

    RETURN totalBalance;
END;

Description

Table booking

Le contenu du champ aircraft_id de la table booking peut être nul. Dans ce cas, c'est une ancienne réservation effectuée sur une ressource non-existante ou qui n'existe plus; aussi nommé réservation orpheline.

Table flight

  • airborne :
    • 0: Pas en l'air ou fermeture de vol : Le pilote a terminé le vol.
    • 1: En l'air ou ouverture de vol : Le pilote remplit le vol avant de voler.

Champs cachés

Les champs suivants ne peuvent être exportés et ne doivent pas être inclus dans une requête SELECT. De plus, un "SELECT *" est interdit quand une des tables contient un champ caché :

  • Table journal : Tous les champs
  • Table log : Tous les champs
  • Table parameter : Tous les champs
  • Table person :
    • hash_password
  • Table structure :
    • address
    • admin_num
    • city
    • country
    • default_notification
    • default_slot_range
    • default_timezone
    • default_view_type
    • email
    • fax
    • first_hour_displayed
    • info_cell
    • lang
    • last_hour_displayed
    • logo
    • logo_name
    • logo_ext
    • logo_size
    • mail_from_address
    • mailing_list_name
    • mailing_list_type
    • min_slot_range
    • name
    • phone
    • state
    • twilight_range
    • usual_profiles
    • welcome_cell
    • zipcode
  • Table oauth_client : Tous les champs
  • Table oauth_access_token : Tous les champs
  • Table oauth_auth_code : Tous les champs
  • Table oauth_refresh_token : Tous les champs

Exemple de requêtes non autorisées : <sql>SELECT * FROM structure;

SELECT hash_password FROM person;

SELECT person.id, validity.* FROM person LEFT JOIN validity ON (person.id=validity.person_id);</sql>