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,
  `custom_label` varchar(255),
  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>