Structure-de-la-base-de-données

Structure

<sql>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;</sql>

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>