Structure de la base de données : Différence entre versions

Aller à : navigation, rechercher
(Page créée avec « =Structure= <sql>-- -- Table structure for table `account` -- CREATE TABLE `account` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `creation_date` date DEFAULT NU... »)
 
(Champs cachés)
(24 révisions intermédiaires par 4 utilisateurs non affichées)
Ligne 1 : Ligne 1 :
 
=Structure=
 
=Structure=
  
<sql>--
+
<sql>create table account
-- Table structure for 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 TABLE `account` (
+
create index idx_owner_id
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
    on account (owner_id);
  `creation_date` date DEFAULT NULL,
+
  `name` text,
+
  `export_account` varchar(255) DEFAULT NULL,
+
  `activated` tinyint(1) unsigned NOT NULL DEFAULT '1',
+
  `category` tinyint(1) unsigned DEFAULT '0',
+
  `account_type` tinyint(1) unsigned DEFAULT '0',
+
  `owner_id` int(10) unsigned NOT NULL DEFAULT '0',
+
  `payment_allowed` tinyint(1) DEFAULT '0',
+
  `budget_id` int(10) unsigned DEFAULT NULL,
+
  `order_num` int(11) DEFAULT NULL,
+
  `accounting_id` int(10) unsigned DEFAULT NULL,
+
  `deactivated_date` datetime DEFAULT NULL,
+
  PRIMARY KEY (`id`),
+
  KEY `idx_owner_id` (`owner_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of account';
+
  
--
+
create table account_entry
-- Table structure for table `account_entry`
+
(
--
+
    id                  int auto_increment
 +
        primary key,
 +
    flow_id            int                            null,
 +
    account_date        datetime                      null,
 +
    account_id          int                            null,
 +
    credit              decimal(15, 5) default 0.00000 null,
 +
    debit              decimal(15, 5) default 0.00000 null,
 +
    payment_type        int unsigned                  null,
 +
    payment_description text                          null,
 +
    person_delivery_id  int unsigned                  null,
 +
    comments            text                          null,
 +
    validated          int(1)        default 0      null,
 +
    exported            int(1)        default 0      null,
 +
    registration_date  datetime                      null,
 +
    budget_id          int unsigned                  null,
 +
    product_id          int unsigned                  null,
 +
    signature          varchar(56)                    null,
 +
    signature_date      bigint(13) unsigned            null,
 +
    lettering          int                            null,
 +
    lettering_date      datetime                      null
 +
)
 +
    comment 'List of account entries';
  
CREATE TABLE `account_entry` (
+
create index idx_account_date
  `id` int(11) NOT NULL AUTO_INCREMENT,
+
    on account_entry (account_date);
  `flow_id` int(11) DEFAULT NULL,
+
  `account_date` datetime DEFAULT NULL,
+
  `account_id` int(11) DEFAULT NULL,
+
  `credit` decimal(15,5) NOT NULL DEFAULT '0.00000',
+
  `debit` decimal(15,5) NOT NULL 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,
+
  PRIMARY KEY (`id`),
+
  KEY `idx_flow_id` (`flow_id`),
+
  KEY `idx_account_date` (`account_date`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of account entries';
+
  
--
+
create index idx_flow_id
-- Table structure for table `account_link`
+
    on account_entry (flow_id);
--
+
  
CREATE TABLE `account_link` (
+
create index idx_signature_date
  `account_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    on account_entry (signature_date);
  `linked_account_id` int(10) unsigned NOT NULL DEFAULT '0',
+
  PRIMARY KEY (`account_id`,`linked_account_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create table account_link
-- Table structure for table `account_type`
+
(
--
+
    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` (
+
create table account_type
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `name` varchar(255) DEFAULT NULL,
+
    id                     int unsigned auto_increment
  `category` tinyint(1) unsigned DEFAULT '0',
+
        primary key,
  `activated` tinyint(1) DEFAULT '1',
+
    name                   varchar(255)                 null,
  `order_num` int(11) DEFAULT NULL,
+
    category               tinyint(1) unsigned default 0 null,
  `accounting_id` int(10) unsigned DEFAULT NULL,
+
    activated             tinyint(1)         default 1 null,
  `export_account_pattern` varchar(255) DEFAULT NULL,
+
    order_num             int                           null,
  PRIMARY KEY (`id`)
+
    accounting_id         int unsigned                 null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    export_account_pattern varchar(255)                 null
 +
);
  
--
+
create table account_type_profile
-- Table structure for 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 `account_type_profile` (
+
create table accounting
  `account_type_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
    id                int unsigned auto_increment
  PRIMARY KEY (`account_type_id`,`profile_id`)
+
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Linked account type\r\nfield and profile';
+
    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
-- Table structure for table `accounting`
+
(
--
+
    accounting_id int unsigned    default 0 not null,
 +
    profile_id    bigint unsigned default 0 not null,
 +
    primary key (accounting_id, profile_id)
 +
);
  
CREATE TABLE `accounting` (
+
create table activity_training
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `name` varchar(255) DEFAULT NULL,
+
    activity_id      int unsigned default 0 not null,
  `unit_name` varchar(255) DEFAULT NULL,
+
    theme_id          int unsigned default 0 not null,
  `symbol` varchar(255) DEFAULT NULL,
+
    student_id        int(10)               null,
  `format` tinyint(1) unsigned DEFAULT NULL,
+
    level_reached    int unsigned          null,
  `decimal_precision` int(10) unsigned DEFAULT '2',
+
    comment          varchar(255)           null,
  PRIMARY KEY (`id`)
+
    validated        tinyint(1)   default 0 not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    checker_person_id int(10)               null,
 +
    checking_date    datetime              null,
 +
    checking_sentence varchar(255)          null,
 +
    primary key (activity_id, theme_id)
 +
);
  
--
+
create table activity_type
-- Table structure for table `accounting_profile`
+
(
--
+
    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 `accounting_profile` (
+
create table aircraft
  `accounting_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
    id              int unsigned    default 0    not null
  PRIMARY KEY (`accounting_id`,`profile_id`)
+
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `activity_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 `activity_type` (
+
create table aircraft_type_allowed_status
  `id` int(10) unsigned NOT NULL,
+
(
  `name` varchar(255) DEFAULT NULL,
+
    aircraft_type_id int unsigned null,
  `order_num` int(11) DEFAULT NULL,
+
    place_num        int unsigned null,
  `activated` tinyint(1) NOT NULL DEFAULT '1',
+
    status_id        int unsigned null
  `color` int(10) unsigned DEFAULT NULL,
+
)
  PRIMARY KEY (`id`)
+
    comment 'List of allowed functions for each aircraft type';
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='list of flight type';
+
  
--
+
create table aircraft_type_mandatory_flight_type
-- Table structure for table `aircraft`
+
(
--
+
    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` (
+
create table aircraft_type_uncomp_flight_type
  `id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
  `type` int(10) unsigned DEFAULT NULL,
+
    aircraft_type_id int unsigned   null,
  `ref_date` datetime DEFAULT NULL,
+
    activity_type_id bigint unsigned null
  `ref_hours` int(11) DEFAULT NULL,
+
)
  `inspection_date` datetime DEFAULT NULL,
+
    comment 'List of uncompatible flight type for each aircraft type';
  `inspection_time` int(11) DEFAULT NULL,
+
  `interval_visit` int(11) unsigned DEFAULT '30000',
+
  `time_alert1` int(11) DEFAULT '6000',
+
  `time_alert2` int(11) DEFAULT '0',
+
  `time_alert3` int(11) DEFAULT '-3000',
+
  `day_alert1` smallint(3) DEFAULT '15',
+
  `day_alert2` smallint(3) DEFAULT '0',
+
  `day_alert3` smallint(3) DEFAULT '-8',
+
  `last_counter` int(11) DEFAULT NULL,
+
  `tolerance_time` int(10) unsigned DEFAULT '6000',
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='ALL AIRCRAFTS OF ALL AIRCLUBS';
+
  
--
+
create table aircraft_type_validity_type
-- Table structure for table `aircraft_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 `aircraft_type` (
+
create table allocation_rule
  `id` int(10) unsigned NOT NULL,
+
(
  `flight_time_formula` varchar(255) DEFAULT '%DURATION',
+
    id         int unsigned auto_increment
  `counter_state` tinyint(2) NOT NULL DEFAULT '-1',
+
        primary key,
  `tolerance` int(11) DEFAULT '0',
+
    title      varchar(255)         not null,
  `autonomy` int(11) DEFAULT '5990',
+
    account_id int(10)             not null,
  `true_air_speed` int(10) unsigned NOT NULL DEFAULT '0',
+
    keyword    varchar(255)         null,
  `digit_counter_number` tinyint(1) unsigned NOT NULL DEFAULT '4',
+
    order_num  int(10)             not null,
  `order_num` int(11) DEFAULT NULL,
+
    amount    tinyint(1)           not null,
  `activated` tinyint(1) unsigned NOT NULL DEFAULT '1',
+
    mandatory  tinyint(1) default 0 not null
  PRIMARY KEY (`id`)
+
);
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Types of aircraft';
+
  
--
+
create table balance
-- Table structure for table `aircraft_type_allowed_status`
+
(
--
+
    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 `aircraft_type_allowed_status` (
+
create table balance_date
  `aircraft_type_id` int(10) unsigned DEFAULT NULL,
+
(
  `place_num` int(10) unsigned DEFAULT NULL,
+
    id          int unsigned auto_increment
  `status_id` int(10) unsigned DEFAULT NULL
+
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of allowed functions for each aircraft type';
+
    balance_date datetime null
 +
);
  
--
+
create table booking
-- Table structure for table `aircraft_type_mandatory_flight_type`
+
(
--
+
    id              int unsigned auto_increment
 +
        primary key,
 +
    booking_group_id int unsigned null,
 +
    start_date      datetime    null,
 +
    end_date        datetime    null
 +
);
  
CREATE TABLE `aircraft_type_mandatory_flight_type` (
+
create table booking_account_entry
  `aircraft_type_id` int(10) unsigned DEFAULT NULL,
+
(
  `flight_type_id` int(10) unsigned DEFAULT NULL
+
    booking_id            int unsigned default 0 not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of compulsory flight type for each aircraft type';
+
    account_entry_flow_id int unsigned default 0 not null,
 +
    primary key (booking_id, account_entry_flow_id)
 +
);
  
--
+
create table booking_activity_type
-- Table structure for table `aircraft_type_uncomp_flight_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 `aircraft_type_uncomp_flight_type` (
+
create table booking_changelog
  `aircraft_type_id` int(10) unsigned DEFAULT NULL,
+
(
  `flight_type_id` int(10) unsigned DEFAULT NULL
+
    id            int unsigned auto_increment
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of uncompatible flight type for each aircraft type';
+
        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
-- Table structure for table `aircraft_type_validity_type`
+
(
--
+
    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 `aircraft_type_validity_type` (
+
create table booking_resource
  `aircraft_type_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
  `validity_type_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    booking_id  int unsigned default 0 not null,
  `check_num` int(10) unsigned NOT NULL DEFAULT '0',
+
    resource_id int unsigned default 0 not null,
  PRIMARY KEY (`aircraft_type_id`,`validity_type_id`,`check_num`)
+
    primary key (booking_id, resource_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Types of qualif required for each aircraft type';
+
);
  
--
+
create table budget
-- Table structure for table `balance`
+
(
--
+
    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 `balance` (
+
create table budget_group
  `account_id` int(10) unsigned NOT NULL,
+
(
  `balance_date_id` int(10) unsigned NOT NULL,
+
    id        int unsigned auto_increment
  `debit` decimal(15,5) NOT NULL DEFAULT '0.00000',
+
        primary key,
  `credit` decimal(15,5) NOT NULL DEFAULT '0.00000',
+
    name      varchar(255) null,
  PRIMARY KEY (`account_id`,`balance_date_id`)
+
    order_num int          null
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
);
  
--
+
create table business_field
-- Table structure for table `balance_date`
+
(
--
+
    id                      int unsigned auto_increment
 +
        primary key,
 +
    variable                varchar(255)                  null,
 +
    label                    varchar(255)                  not null,
 +
    value_type              varchar(40)                    not null,
 +
    category                varchar(255)                  not null,
 +
    order_num                int                            not null,
 +
    dsn                      varchar(20) default 'customer' null,
 +
    compulsory_fill          tinyint(1)  default 0          null,
 +
    linked_category          varchar(255)                  null,
 +
    linked_business_field_id int unsigned                  null,
 +
    linked_field_name        varchar(255)                  null,
 +
    max_display              int(10)    default -1        null,
 +
    formula                  text                          null,
 +
    default_value            text                          null,
 +
    placeholder              text                          null,
 +
    access_level_required    tinyint    default 0          not null,
 +
    constraint variable
 +
        unique (variable)
 +
)
 +
    comment 'List of extra form field';
  
CREATE TABLE `balance_date` (
+
create table business_field_activity_type
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `balance_date` datetime DEFAULT NULL,
+
    business_field_id      int unsigned   default 0 not null,
  PRIMARY KEY (`id`)
+
    activity_type_id        bigint unsigned default 0 not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `booking`
+
(
--
+
    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 `booking` (
+
create table business_field_group
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `booking_group_id` int(10) unsigned DEFAULT NULL,
+
    id       int unsigned auto_increment
  `start_date` datetime DEFAULT NULL,
+
        primary key,
  `end_date` datetime DEFAULT NULL,
+
    label    varchar(255) null,
  PRIMARY KEY (`id`)
+
    order_num int          null
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='RECORDS ALL THE SLOTS OF ALL AIRCRAFTS OF ALL AIRCLUBS';
+
);
  
--
+
create table criteria
-- Table structure for table `booking_account_entry`
+
(
--
+
    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 `booking_account_entry` (
+
create table criteria_profile
  `booking_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
  `account_entry_flow_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    criteria_id int unsigned   not null,
  PRIMARY KEY (`booking_id`,`account_entry_flow_id`)
+
    profile_id  bigint unsigned not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    primary key (criteria_id, profile_id)
 +
);
  
--
+
create table customer_bill_entry
-- Table structure for table `booking_activity_type`
+
(
--
+
    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 TABLE `booking_activity_type` (
+
create index idx_account_entry_flow_id
  `booking_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    on customer_bill_entry (account_entry_flow_id);
  `activity_type_id` int(10) unsigned NOT NULL DEFAULT '0',
+
  PRIMARY KEY (`booking_id`,`activity_type_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create index idx_account_entry_id
-- Table structure for table `booking_changelog`
+
    on customer_bill_entry (account_entry_id);
--
+
  
CREATE TABLE `booking_changelog` (
+
create table customer_bill_file
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `booking_id` int(10) unsigned NOT NULL,
+
    id       int unsigned auto_increment
  `person_id` int(10) unsigned NOT NULL,
+
        primary key,
   `changelog_date` datetime NOT NULL,
+
    file_type varchar(255) null,
  `booking_state` text,
+
    content   longblob    null
  PRIMARY KEY (`id`)
+
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    comment 'List of customer bill stored file';
  
--
+
create table customer_receipt_entry
-- Table structure for table `booking_person`
+
(
--
+
    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 TABLE `booking_person` (
+
create index idx_account_entry_flow_id
  `booking_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    on customer_receipt_entry (account_entry_flow_id);
  `person_id` int(10) unsigned NOT NULL DEFAULT '0',
+
  `place_num` int(10) unsigned DEFAULT NULL,
+
  `status_id` int(10) unsigned DEFAULT NULL,
+
  PRIMARY KEY (`booking_id`,`person_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create index idx_account_entry_id
-- Table structure for table `booking_resource`
+
    on customer_receipt_entry (account_entry_id);
--
+
  
CREATE TABLE `booking_resource` (
+
create table customer_receipt_file
  `booking_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
  `resource_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    id        int unsigned auto_increment
   PRIMARY KEY (`booking_id`,`resource_id`)
+
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    file_type varchar(255) null,
 +
    content   longblob    null
 +
);
  
--
+
create table default_display
-- Table structure for table `budget`
+
(
--
+
    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 `budget` (
+
create table default_multi_display
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `group_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    resource_type char(30)     not null,
   `name` varchar(255) DEFAULT NULL,
+
    person_id    int unsigned not null,
  `order_num` int(11) DEFAULT NULL,
+
    resource_id   varchar(100) not null,
  `export_code` varchar(255) DEFAULT NULL,
+
    display_value text        null,
  PRIMARY KEY (`id`)
+
    primary key (resource_type, person_id, resource_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
);
  
--
+
create table email_sent
-- Table structure for table `budget_group`
+
(
--
+
    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 `budget_group` (
+
create table email_sent_recipient
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `name` varchar(255) DEFAULT NULL,
+
    id                   bigint unsigned auto_increment
  `order_num` int(11) DEFAULT NULL,
+
        primary key,
  PRIMARY KEY (`id`)
+
    email_sent_id        bigint unsigned  not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `business_field`
+
    on email_sent_recipient (email_sent_id);
--
+
  
CREATE TABLE `business_field` (
+
create table exceptionnal_inst_date
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `variable` varchar(255) DEFAULT NULL,
+
    id         int unsigned auto_increment
  `label` varchar(255) NOT NULL,
+
        primary key,
  `value_type` varchar(40) NOT NULL,
+
    person_id  int unsigned        null,
   `category` varchar(255) NOT NULL,
+
    start_date datetime            null,
   `order_num` int(11) NOT NULL,
+
    end_date   datetime            null,
  `dsn` varchar(20) DEFAULT 'customer',
+
    presence   tinyint(1) unsigned null
  `compulsory_fill` tinyint(1) DEFAULT '0',
+
);
  `linked_category` varchar(255) DEFAULT NULL,
+
  `linked_extra_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,
+
  PRIMARY KEY (`id`),
+
  UNIQUE KEY `variable` (`variable`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of extra form field';
+
  
--
+
create table extra_field_profile
-- Table structure for table `business_field_activity_type`
+
(
--
+
    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 `business_field_activity_type` (
+
create table facebook
  `business_field_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
  `activity_type_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    id          int unsigned auto_increment
  `business_field_group_id` int(10) unsigned NOT NULL DEFAULT '0',
+
        primary key,
  `visibility_type` tinyint(1) unsigned DEFAULT NULL,
+
    category    int unsigned not null,
   PRIMARY KEY (`business_field_id`,`activity_type_id`)
+
    owner_id    int unsigned not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    small      blob        null,
 +
    original    mediumblob   not null,
 +
    label      text        null,
 +
    description text        null
 +
);
  
--
+
create table favorite_icao
-- Table structure for table `business_field_content`
+
(
--
+
    icao varchar(6) default '' not null
 +
        primary key
 +
)
 +
    comment 'favorite airfield list';
  
CREATE TABLE `business_field_content` (
+
create table favorite_report
  `category_id` int(10) unsigned NOT NULL,
+
(
  `extra_field_id` int(10) unsigned NOT NULL,
+
    report_id int unsigned not null
  `person_id` int(10) unsigned NOT NULL DEFAULT '0',
+
        primary key
  `content` text,
+
);
  `placeholder` text,
+
  PRIMARY KEY (`category_id`,`extra_field_id`,`person_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Content of extra form field';
+
  
--
+
create table favorite_report_business_field
-- Table structure for table `business_field_group`
+
(
--
+
    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 `business_field_group` (
+
create table favorite_report_profile
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `label` varchar(255) DEFAULT NULL,
+
    report_id  int unsigned             not null,
  `order_num` int(11) DEFAULT NULL,
+
    profile_id bigint unsigned default 0 not null,
  PRIMARY KEY (`id`)
+
    primary key (report_id, profile_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
);
  
--
+
create table fhp_aircraft_type
-- Table structure for table `club`
+
(
--
+
    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 `club` (
+
create table fhp_flight_type
  `id` int(10) NOT NULL DEFAULT '0',
+
(
  `name` varchar(255) DEFAULT NULL,
+
    fhp_id          int(10)             default 0 not null,
  `info_cell` text,
+
    activity_type_id bigint unsigned     default 0 not null,
  `logo` longblob,
+
    excluded        tinyint(1) unsigned default 0 null,
  `logo_name` varchar(255) DEFAULT NULL,
+
    primary key (fhp_id, activity_type_id)
  `logo_ext` varchar(25) DEFAULT NULL,
+
)
  `logo_size` int(11) DEFAULT NULL,
+
    comment 'flight hour pricing flight type list';
  `first_hour_displayed` time DEFAULT NULL,
+
  `last_hour_displayed` time DEFAULT NULL,
+
  `usual_profiles` bigint(20) unsigned DEFAULT NULL,
+
  `icao` varchar(6) DEFAULT NULL,
+
  `default_slot_range` int(10) unsigned DEFAULT NULL,
+
  `min_slot_range` tinyint(3) unsigned DEFAULT NULL,
+
  `twilight_range` tinyint(3) unsigned DEFAULT NULL,
+
  `mailing_list_name` varchar(255) DEFAULT NULL,
+
  `mailing_list_type` varchar(255) DEFAULT NULL,
+
  `club_site_url` varchar(255) DEFAULT NULL,
+
  `default_timezone` varchar(255) NOT NULL,
+
  `lang` varchar(255) NOT NULL,
+
  `admin_num` int(10) unsigned NOT NULL,
+
  `default_view_type` int(10) unsigned DEFAULT NULL,
+
  `address` varchar(255) DEFAULT NULL,
+
  `zipcode` varchar(255) DEFAULT NULL,
+
  `city` varchar(255) DEFAULT NULL,
+
  `state` varchar(255) DEFAULT NULL,
+
  `country` varchar(255) DEFAULT NULL,
+
  `phone` varchar(255) DEFAULT NULL,
+
  `fax` varchar(255) DEFAULT NULL,
+
  `email` varchar(255) DEFAULT NULL,
+
  `default_notification` int(3) unsigned DEFAULT NULL,
+
  `welcome_cell` text,
+
  `business` text,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='DESCRIPTION OF ALL AIRCLUBS';
+
  
--
+
create table fhp_profile
-- Table structure for table `criteria`
+
(
--
+
    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 `criteria` (
+
create table file
  `id` int(11) NOT NULL AUTO_INCREMENT,
+
(
  `label` varchar(255) NOT NULL,
+
    id           int unsigned auto_increment
  `predicate` text NOT NULL,
+
        primary key,
  `order_num` int(11) DEFAULT NULL,
+
    name          varchar(255)       null,
  `dsn` varchar(20) DEFAULT 'customer',
+
    content      longblob            null,
  PRIMARY KEY (`id`)
+
    file_type    varchar(255)       null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `criteria_profile`
+
(
--
+
    id    int unsigned auto_increment
 +
        primary key,
 +
    label varchar(255) null
 +
);
  
CREATE TABLE `criteria_profile` (
+
create table flight
   `criteria_id` int(10) unsigned NOT NULL,
+
(
  `profile_id` bigint(20) unsigned NOT NULL,
+
    id                    int unsigned auto_increment
  PRIMARY KEY (`criteria_id`,`profile_id`)
+
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `customer_bill_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 TABLE `customer_bill_entry` (
+
create index idx_account_entry_id
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
    on flight_account_entry (account_entry_id);
  `flow_id` int(10) unsigned DEFAULT NULL,
+
  `account_entry_id` int(10) unsigned DEFAULT NULL,
+
  `account_entry_flow_id` int(10) unsigned DEFAULT NULL,
+
  `owner_category` int(10) unsigned DEFAULT NULL,
+
  `owner_id` int(10) unsigned DEFAULT NULL,
+
  `customer_bill_id` int(10) unsigned DEFAULT NULL,
+
  `product_id` int(10) unsigned DEFAULT NULL,
+
  `qty` decimal(15,5) DEFAULT '0.00000',
+
  `unit_price` decimal(15,5) DEFAULT '0.00000',
+
  `debit` decimal(15,5) DEFAULT '0.00000',
+
  `credit` decimal(15,5) DEFAULT '0.00000',
+
  `bill_date` datetime DEFAULT NULL,
+
  PRIMARY KEY (`id`),
+
  KEY `idx_account_entry_id` (`account_entry_id`),
+
  KEY `idx_account_entry_flow_id` (`account_entry_flow_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create table flight_hours_pricing
-- Table structure for table `customer_bill_file`
+
(
--
+
    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 `customer_bill_file` (
+
create table flight_pilot
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `file_type` varchar(255) DEFAULT NULL,
+
    flight_id int unsigned           not null,
  `content` longblob,
+
    pilot_id  int unsigned          not null,
  PRIMARY KEY (`id`)
+
    status_id int unsigned          null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `default_display`
+
(
--
+
    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 `default_display` (
+
create table flight_track
  `person_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
  `display_key` varchar(255) NOT NULL DEFAULT '',
+
    id        int unsigned auto_increment
  `display_value` text,
+
        primary key,
  PRIMARY KEY (`person_id`,`display_key`)
+
    flight_id int(10)   not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    track    mediumblob not null
 +
);
  
--
+
create table flight_type_mandatory_validity_type
-- Table structure for table `default_multi_display`
+
(
--
+
    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 `default_multi_display` (
+
create table import
  `resource_type` char(30) NOT NULL,
+
(
  `person_id` int(10) unsigned NOT NULL,
+
    id                  int unsigned auto_increment
  `resource_id` int(10) unsigned NOT NULL,
+
        primary key,
  `display_value` text,
+
    label              varchar(255)                         not null,
   PRIMARY KEY (`resource_type`,`person_id`,`resource_id`)
+
    order_num          int                                   null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `email_sent`
+
(
--
+
    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 `email_sent` (
+
create table journal
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `message_id` varchar(150) NOT NULL,
+
    id       int unsigned auto_increment
  `sender` varchar(255) NOT NULL,
+
        primary key,
  `title` varchar(255) NOT NULL,
+
    login    varchar(255)           null,
  `category` tinyint(3) unsigned NOT NULL,
+
    date_log  datetime              null,
   `category_id` int(10) unsigned NOT NULL,
+
    rights    text                  null,
  PRIMARY KEY (`id`),
+
    rights2   text                  null,
  UNIQUE KEY `message_id_UNIQUE` (`message_id`)
+
    action    varchar(255)           null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    person_id int unsigned default 0 null
 +
)
 +
    comment 'list of logs';
  
--
+
create index idx_action
-- Table structure for table `email_sent_recipient`
+
    on journal (action);
--
+
  
CREATE TABLE `email_sent_recipient` (
+
create index idx_date_log
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+
    on journal (date_log);
  `email_sent_id` bigint(20) unsigned NOT NULL,
+
  `person_id` int(10) unsigned NOT NULL,
+
  `email` varchar(255) DEFAULT NULL,
+
  `recipient_type` tinyint(3) unsigned DEFAULT NULL,
+
  `smtp_status_category` varchar(30) DEFAULT NULL,
+
  `email_status` tinyint(3) unsigned DEFAULT NULL,
+
  `update_date` datetime NOT NULL,
+
  PRIMARY KEY (`id`),
+
  KEY `email_sent_id_idx` (`email_sent_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create table key_alert
-- Table structure for table `exceptionnal_inst_date`
+
(
--
+
    id        int unsigned auto_increment
 +
        primary key,
 +
    alert_date datetime              null,
 +
    status    int unsigned default 0 not null
 +
)
 +
    comment 'Key alerts';
  
CREATE TABLE `exceptionnal_inst_date` (
+
create table key_assignment
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `person_id` int(10) unsigned NOT NULL,
+
    key_id      tinyint(2) unsigned default 0 not null
  `start_date` datetime DEFAULT NULL,
+
        primary key,
  `end_date` datetime DEFAULT NULL,
+
    key_name    tinytext                      null,
   `presence` tinyint(1) unsigned DEFAULT NULL,
+
    aircraft_id int unsigned        default 0 null,
  PRIMARY KEY (`id`)
+
    key_state   tinyint(1) unsigned default 0 null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    key_word    bigint unsigned    default 0 null
 +
);
  
--
+
create table key_host
-- Table structure for table `extra_field_profile`
+
(
--
+
    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 `extra_field_profile` (
+
create table key_log
  `extra_field_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
   `booking_popup_display_4_profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
    id        int unsigned auto_increment
  PRIMARY KEY (`extra_field_id`,`booking_popup_display_4_profile_id`)
+
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `facebook`
+
(
--
+
    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 `facebook` (
+
create table log
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `category` int(10) unsigned NOT NULL,
+
    journal_id  int unsigned not null,
  `owner_id` int(10) unsigned NOT NULL,
+
    action      varchar(255) null,
  `small` blob,
+
    table_name  varchar(255) null,
  `original` mediumblob NOT NULL,
+
    field_name  varchar(255) null,
  `label` text,
+
    field_value varchar(255) null
  `description` text,
+
)
  PRIMARY KEY (`id`)
+
    comment 'part of logs';
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create index idx_field_value
-- Table structure for table `favorite_icao`
+
    on log (field_value);
--
+
  
CREATE TABLE `favorite_icao` (
+
create index idx_journal_id
  `icao` varchar(6) NOT NULL DEFAULT '',
+
    on log (journal_id);
  PRIMARY KEY (`icao`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='favorite airfield list';
+
  
--
+
create index journal_id
-- Table structure for table `favorite_report`
+
    on log (journal_id);
--
+
  
CREATE TABLE `favorite_report` (
+
create table logger
  `report_id` int(10) unsigned NOT NULL,
+
(
  PRIMARY KEY (`report_id`)
+
    id            int unsigned auto_increment
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
        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
-- Table structure for table `favorite_report_profile`
+
(
--
+
    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 `favorite_report_profile` (
+
create table m_component
  `report_id` int(10) unsigned NOT NULL,
+
(
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
    id                  int unsigned auto_increment
  PRIMARY KEY (`report_id`,`profile_id`)
+
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `fhp_aircraft_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 `fhp_aircraft_type` (
+
create table m_component_type_parentality
  `fhp_id` int(10) NOT NULL DEFAULT '0',
+
(
  `aircraft_type_id` int(10) NOT NULL DEFAULT '0',
+
    m_component_type_id        int unsigned not null,
  PRIMARY KEY (`fhp_id`,`aircraft_type_id`)
+
    m_component_type_parent_id int unsigned not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='flight hour pricing aircraft type list';
+
    primary key (m_component_type_id, m_component_type_parent_id)
 +
);
  
--
+
create table m_history
-- Table structure for table `fhp_flight_type`
+
(
--
+
    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 `fhp_flight_type` (
+
create table m_operation
  `fhp_id` int(10) NOT NULL DEFAULT '0',
+
(
  `flight_type_id` int(10) NOT NULL DEFAULT '0',
+
    id                  int unsigned auto_increment
  `excluded` tinyint(1) unsigned DEFAULT '0',
+
        primary key,
  PRIMARY KEY (`fhp_id`,`flight_type_id`)
+
    content            text        null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='flight hour pricing flight type list';
+
    m_component_type_id int unsigned not null,
 +
    order_num          int          not null
 +
);
  
--
+
create table maintenance_history
-- Table structure for table `fhp_profile`
+
(
--
+
    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 `fhp_profile` (
+
create table maintenance_program
  `fhp_id` int(10) unsigned NOT NULL,
+
(
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
    id                                    int unsigned auto_increment
  `place_num` tinyint(1) unsigned NOT NULL DEFAULT '0',
+
        primary key,
  PRIMARY KEY (`fhp_id`,`profile_id`,`place_num`)
+
    resource_id                          int unsigned                  null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `file`
+
(
--
+
    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 `file` (
+
create table map_logger_monitoring
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `name` varchar(255) DEFAULT NULL,
+
    record_id  int unsigned not null
  `content` mediumblob,
+
        primary key,
  `file_type` varchar(255) DEFAULT NULL,
+
    count_data int unsigned not null
  `category` tinyint(1) unsigned DEFAULT NULL,
+
)
  PRIMARY KEY (`id`)
+
    comment 'link between flight and track';
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create table map_track
-- Table structure for table `flight`
+
(
--
+
    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 `flight` (
+
create table nationality
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `aircraft_id` int(10) unsigned DEFAULT NULL,
+
    code  char(2) default '' not null
  `start_date` datetime DEFAULT NULL,
+
        primary key,
  `duration` int(10) unsigned DEFAULT NULL,
+
    label varchar(255)       not null
  `flight_type_id` int(10) unsigned DEFAULT NULL,
+
);
  `people_onboard` int(10) unsigned DEFAULT NULL,
+
  `departure_location_id` int(10) unsigned DEFAULT NULL,
+
  `arrival_location_id` int(10) unsigned DEFAULT NULL,
+
  `counter_departure` int(10) unsigned DEFAULT NULL,
+
  `counter_arrival` int(10) unsigned DEFAULT NULL,
+
  `landing_number` int(10) unsigned DEFAULT NULL,
+
  `airborne` tinyint(1) unsigned DEFAULT NULL,
+
  `validated` tinyint(3) unsigned DEFAULT '0',
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='list of flight';
+
  
--
+
create table parameter
-- Table structure for table `flight_account_entry`
+
(
--
+
    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 `flight_account_entry` (
+
create table payment_distribution
  `flight_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
  `account_entry_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    payment_id        int unsigned       default 0 not null
   PRIMARY KEY (`flight_id`,`account_entry_id`),
+
        primary key,
  KEY `idx_account_entry_id` (`account_entry_id`)
+
    account_club_id    int unsigned                  null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='flight account entry join';
+
    person_delivery    tinyint(1) unsigned default 0 null,
 +
    member_budget_id   int unsigned                  null,
 +
    treasury_budget_id int unsigned                  null
 +
)
 +
    comment 'payment distribution description';
  
--
+
create table payment_summary_file
-- Table structure for table `flight_hours_pricing`
+
(
--
+
    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 `flight_hours_pricing` (
+
create table payment_type
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `name` text,
+
    id               int unsigned auto_increment
  `price_formula` text,
+
        primary key,
  `left_account_id` int(10) unsigned DEFAULT NULL,
+
    name             text                         null,
  `right_account_id` int(10) unsigned DEFAULT NULL,
+
    text_field_label text                         null,
  `left_account_type` tinyint(1) unsigned DEFAULT '0',
+
    only_admin      tinyint(1) unsigned default 0 not null,
  `right_account_type` tinyint(1) unsigned DEFAULT '0',
+
    order_num       int                           null,
  `credit_budget_id` int(10) unsigned DEFAULT NULL,
+
    pos_key_id      int unsigned                 null
  `debit_budget_id` int(10) unsigned DEFAULT NULL,
+
)
  `order_num` int(11) DEFAULT NULL,
+
    comment 'type of payment description';
  `product_id` int(10) unsigned DEFAULT NULL,
+
  `sale_trigger_id` int(10) unsigned DEFAULT '0',
+
  `query` text,
+
  `variable_formula` varchar(255) DEFAULT NULL,
+
  `extra_field_id` int(10) unsigned DEFAULT NULL,
+
  `debit_bill_num` int(10) unsigned DEFAULT NULL,
+
  `credit_bill_num` int(10) unsigned DEFAULT NULL,
+
  `qty_formula` text,
+
  `unit_price_formula` text,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='list of flight hours pricing formula';
+
  
--
+
create table person
-- Table structure for table `flight_pilot`
+
(
--
+
    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 `flight_pilot` (
+
create table person_awaiting_activation
  `flight_id` int(10) unsigned NOT NULL,
+
(
  `pilot_id` int(10) unsigned NOT NULL,
+
    id              int unsigned auto_increment
  `status_id` int(10) unsigned DEFAULT NULL,
+
        primary key,
  `num` int(10) unsigned NOT NULL DEFAULT '0',
+
    login          varchar(255)       not null,
  PRIMARY KEY (`flight_id`,`pilot_id`,`num`)
+
    ip              varchar(255)       not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='list of crew for each flight';
+
    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
-- Table structure for table `flight_tank_qty`
+
(
--
+
    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 `flight_tank_qty` (
+
create table profile
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `flight_id` int(10) unsigned NOT NULL,
+
    id               bigint unsigned default 0 not null
  `tank_id` int(10) unsigned NOT NULL,
+
        primary key,
  `quantity` varchar(255) NOT NULL DEFAULT '0.00',
+
    name              varchar(255)             null,
  `after_flight` tinyint(1) NOT NULL DEFAULT '0',
+
    permits          int unsigned             null,
  `account_id` int(10) unsigned DEFAULT NULL,
+
    permits2          int unsigned    default 0 not null,
  `pay_type` tinyint(1) unsigned DEFAULT NULL,
+
    permits3          int unsigned    default 0 not null,
  PRIMARY KEY (`id`)
+
    pictogram        int unsigned             null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    default_status_id int unsigned             null
 +
);
  
--
+
create table profile_accounting_notification
-- Table structure for table `flight_track`
+
(
--
+
    profile_id    bigint unsigned default 0 not null,
 +
    accounting_id int unsigned    default 0 not null,
 +
    primary key (profile_id, accounting_id)
 +
);
  
CREATE TABLE `flight_track` (
+
create table profile_extra_field_join
  `flight_id` int(10) unsigned NOT NULL,
+
(
  `track_id` int(10) unsigned NOT NULL,
+
    profile_id        bigint unsigned default 0 not null,
  PRIMARY KEY (`track_id`,`flight_id`)
+
    business_field_id int unsigned   default 0 not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Link between flight and track';
+
    primary key (profile_id, business_field_id)
 +
);
  
--
+
create table profile_profile_view
-- Table structure for table `flight_type_mandatory_validity_type`
+
(
--
+
    profile_id          bigint unsigned not null,
 +
    viewable_profile_id bigint unsigned not null,
 +
    primary key (profile_id, viewable_profile_id)
 +
);
  
CREATE TABLE `flight_type_mandatory_validity_type` (
+
create table profile_required_account_type
  `flight_type_id` int(10) unsigned NOT NULL,
+
(
  `validity_type_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    profile_id      bigint unsigned default 0 not null,
  PRIMARY KEY (`flight_type_id`,`validity_type_id`)
+
    account_type_id int unsigned   default 0 not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='list of mandatory qualification for each flight type';
+
    primary key (profile_id, account_type_id)
 +
);
  
--
+
create table profile_resource_type_place
-- Table structure for table `import`
+
(
--
+
    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 `import` (
+
create table profile_resource_type_view
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `label` varchar(255) NOT NULL,
+
    profile_id                bigint unsigned not null,
  `order_num` int(11) DEFAULT NULL,
+
    viewable_resource_type_id int unsigned    not null,
  `import_file_type` varchar(255) NOT NULL DEFAULT 'csv-comma-CRLF',
+
    primary key (profile_id, viewable_resource_type_id)
  `match_query` text,
+
);
  `uptodate_test_query` text,
+
  `update_query` text,
+
  `update_activated` tinyint(1) NOT NULL DEFAULT '0',
+
  `login` varchar(255) DEFAULT NULL,
+
  `hash_password` varchar(255) DEFAULT NULL,
+
  `sync_task_name` varchar(255) DEFAULT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create table profile_validity_type_join
-- Table structure for table `ip_stopped`
+
(
--
+
    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 `ip_stopped` (
+
create table profile_validity_type_notification
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `ip` varchar(255) NOT NULL,
+
    profile_id      bigint unsigned default 0 not null,
  `counter` tinyint(1) unsigned NOT NULL,
+
    validity_type_id int unsigned    default 0 not null,
  `expire_date` datetime NOT NULL,
+
    primary key (profile_id, validity_type_id)
  PRIMARY KEY (`id`)
+
);
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='BLACKLISTED IP';
+
  
--
+
create table psp_return
-- Table structure for table `journal`
+
(
--
+
    id                int unsigned auto_increment
 +
        primary key,
 +
    psp_transaction_id int unsigned null,
 +
    bank_answer        text        null,
 +
    bank_misc          text        null
 +
);
  
CREATE TABLE `journal` (
+
create table psp_transaction
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `login` varchar(255) DEFAULT NULL,
+
    id               int unsigned auto_increment
  `date_log` datetime DEFAULT NULL,
+
        primary key,
  `rights` text,
+
    credit_account_id int unsigned          not null,
  `rights2` text,
+
    debit_account_id  int unsigned          not null,
  `action` varchar(255) DEFAULT NULL,
+
    transaction_date  datetime              not null,
   `person_id` int(10) unsigned DEFAULT '0',
+
    amount            float                  not null,
  PRIMARY KEY (`id`),
+
    description      varchar(255)           null,
  KEY `idx_date_log` (`date_log`),
+
    payment_type_id   int unsigned           not null,
  KEY `idx_action` (`action`(8))
+
    state            int unsigned default 0 not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='list of logs';
+
    token            text                  null
 +
);
  
--
+
create table psp_transaction_account_entry
-- Table structure for table `key_alert`
+
(
--
+
    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 `key_alert` (
+
create table regular_presence_inst_date
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `alert_date` datetime DEFAULT NULL,
+
    id         int unsigned auto_increment
  `status` int(10) unsigned NOT NULL DEFAULT '0',
+
        primary key,
   PRIMARY KEY (`id`)
+
    person_id  int unsigned     null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Key alerts';
+
    start_day  tinyint unsigned null,
 +
    end_day    tinyint unsigned null,
 +
    start_hour time            null,
 +
    end_hour   time            null
 +
);
  
--
+
create table resource
-- Table structure for table `key_assignment`
+
(
--
+
    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 `key_assignment` (
+
create table resource_exceptional_availability
  `key_id` tinyint(2) unsigned NOT NULL DEFAULT '0',
+
(
  `key_name` tinytext,
+
    id          int unsigned auto_increment
  `aircraft_id` int(10) unsigned DEFAULT '0',
+
        primary key,
  `key_state` tinyint(1) unsigned DEFAULT '0',
+
    resource_id int unsigned       null,
  `key_word` bigint(20) unsigned DEFAULT '0',
+
    start_date  datetime            null,
  PRIMARY KEY (`key_id`)
+
    end_date    datetime            null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='key assignment description';
+
    presence    tinyint(1) unsigned null
 +
);
  
--
+
create table resource_regular_availability
-- Table structure for table `key_host`
+
(
--
+
    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 `key_host` (
+
create table resource_type
  `id` tinyint(2) NOT NULL AUTO_INCREMENT,
+
(
  `timeout` tinyint(2) NOT NULL DEFAULT '10',
+
    id                   int unsigned auto_increment
  `num_key` tinyint(2) unsigned NOT NULL DEFAULT '8',
+
        primary key,
  `ipkey` varchar(50) NOT NULL DEFAULT '127.0.0.1',
+
    name                varchar(255)                   null,
  `httpport` int(11) NOT NULL DEFAULT '4080',
+
    category            int unsigned                   null,
  PRIMARY KEY (`id`)
+
    seats_available      int                default -1 null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='key host configuration';
+
    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
-- Table structure for table `key_log`
+
(
--
+
    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 `key_log` (
+
create table sale_2_stock
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `log_date` datetime DEFAULT NULL,
+
    id                           int unsigned auto_increment
  `action` varchar(255) DEFAULT NULL,
+
        primary key,
  `message` varchar(255) DEFAULT NULL,
+
    product_id                  int unsigned   null,
  `key_id` int(10) unsigned DEFAULT NULL,
+
    stock_id                    int unsigned   not null,
  `person_id` int(10) unsigned DEFAULT NULL,
+
    stock_variation_qty_per_sale float default 0 not null
  `xmlrpc` int(10) unsigned DEFAULT NULL,
+
);
  `error` varchar(255) DEFAULT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Key logs';
+
  
--
+
create table sale_2_validity_type
-- Table structure for table `location`
+
(
--
+
    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 TABLE `location` (
+
create index idx_product
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
    on sale_2_validity_type (product_id);
  `name` varchar(64) NOT NULL,
+
  `icao_name` varchar(6) DEFAULT NULL,
+
  `latitude` double DEFAULT NULL,
+
  `longitude` double DEFAULT NULL,
+
  `altitude` int(7) DEFAULT NULL,
+
  `weather_station` int(1) unsigned DEFAULT NULL,
+
  `asked_counter` bigint(20) NOT NULL DEFAULT '0',
+
  PRIMARY KEY (`id`),
+
  UNIQUE KEY `icao_name` (`icao_name`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='airfields coord';
+
  
--
+
create table sale_pricing
-- Table structure for table `log`
+
(
--
+
    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 `log` (
+
create table sale_pricing_product
  `journal_id` int(10) unsigned NOT NULL,
+
(
  `action` varchar(255) DEFAULT NULL,
+
    sale_pricing_id int unsigned default 0 not null,
  `table_name` varchar(255) DEFAULT NULL,
+
    product_id      int unsigned default 0 not null,
  `field_name` varchar(255) DEFAULT NULL,
+
    primary key (sale_pricing_id, product_id)
  `field_value` varchar(255) DEFAULT NULL,
+
);
  KEY `idx_journal_id` (`journal_id`),
+
  KEY `idx_field_value` (`field_value`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='part of logs';
+
  
--
+
create table sale_pricing_profile
-- Table structure for table `logger`
+
(
--
+
    sale_pricing_id int unsigned              not null,
 +
    profile_id      bigint unsigned default 0 not null,
 +
    primary key (sale_pricing_id, profile_id)
 +
);
  
CREATE TABLE `logger` (
+
create table sale_trigger
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `serial_number` int(10) unsigned DEFAULT NULL,
+
    id             int(10) auto_increment
  `color` varchar(20) NOT NULL DEFAULT 'red',
+
        primary key,
  `activated` tinyint(1) unsigned NOT NULL DEFAULT '1',
+
    name          varchar(255)                 null,
  `order_num` int(10) unsigned NOT NULL,
+
    event          varchar(255)                 null,
  PRIMARY KEY (`id`)
+
    locked        tinyint(1) unsigned default 0 not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Logger parameters';
+
    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
-- Table structure for table `login_stopped`
+
(
--
+
    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 `login_stopped` (
+
create table statistic
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `login` varchar(255) NOT NULL,
+
    name      varchar(255)               not null,
  `counter` tinyint(3) unsigned NOT NULL,
+
    of_version tinyint(3)     default 0    not null,
  `expire_date` datetime NOT NULL,
+
    value      decimal(15, 2) default 0.00 not null,
  PRIMARY KEY (`id`)
+
    primary key (name, of_version)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='BLACKLISTED LOGIN';
+
);
  
--
+
create table status
-- Table structure for table `m_component`
+
(
--
+
    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 `m_component` (
+
create table stock
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `parent_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    id           int unsigned auto_increment
  `m_component_type_id` int(10) unsigned DEFAULT NULL,
+
        primary key,
  `resource_id` int(10) unsigned DEFAULT NULL,
+
    label        varchar(255) not null,
  `order_num` int(11) NOT NULL,
+
    stock_type_id int unsigned not null
  `serial_number` varchar(255) DEFAULT NULL,
+
);
  `brandnew_date` datetime DEFAULT NULL,
+
  `activated` tinyint(1) unsigned NOT NULL DEFAULT '1',
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create table stock_level
-- Table structure for table `m_component_type`
+
(
--
+
    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 `m_component_type` (
+
create table stock_type
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `parent_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    id   int unsigned auto_increment
  `resource_type_id` int(11) DEFAULT NULL,
+
        primary key,
  `m_classification_id` int(11) DEFAULT NULL,
+
    label varchar(255) not null,
  `is_maintenance_check` tinyint(1) unsigned NOT NULL DEFAULT '1',
+
    unit  varchar(255) not null
  `order_num` int(11) NOT NULL,
+
);
  `label` varchar(255) DEFAULT NULL,
+
  `description` varchar(255) DEFAULT NULL,
+
  `manufacturer` varchar(255) DEFAULT NULL,
+
  `manufacturer_reference` varchar(255) DEFAULT NULL,
+
  `part_number` varchar(255) DEFAULT NULL,
+
  `periodicity` int(10) unsigned DEFAULT NULL,
+
  `tolerance` int(10) unsigned NOT NULL DEFAULT '0',
+
  `calendar_periodicity` int(10) unsigned DEFAULT NULL,
+
  `calendar_tolerance` int(10) unsigned NOT NULL DEFAULT '0',
+
  `activated` tinyint(1) unsigned NOT NULL DEFAULT '1',
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create table stock_variation
-- Table structure for table `m_component_type_parentality`
+
(
--
+
    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 `m_component_type_parentality` (
+
create table stock_variation_account_entry
  `m_component_type_id` int(10) unsigned NOT NULL,
+
(
  `m_component_type_parent_id` int(10) unsigned NOT NULL,
+
    product_id            int unsigned default 0 not null,
  PRIMARY KEY (`m_component_type_id`,`m_component_type_parent_id`)
+
    stock_variation_id    int unsigned           not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `m_history`
+
    on stock_variation_account_entry (account_entry_flow_id);
--
+
  
CREATE TABLE `m_history` (
+
create table structure
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `m_component_id` int(10) unsigned NOT NULL,
+
    id                           int(10)         default 0 not null
  `install_date` datetime DEFAULT NULL,
+
        primary key,
  `remove_date` datetime DEFAULT NULL,
+
    name                          varchar(255)             null,
  `total_hours_on_install` int(10) unsigned NOT NULL DEFAULT '0',
+
    info_cell                    text                      null,
  `threshold_hours` int(10) unsigned NOT NULL DEFAULT '0',
+
    logo                          longblob                  null,
  `threshold_date` datetime DEFAULT NULL,
+
    logo_name                    varchar(255)             null,
  `threshold_date_locked` tinyint(1) unsigned NOT NULL DEFAULT '0',
+
    logo_ext                      varchar(25)              null,
  `threshold_hours_locked` tinyint(1) unsigned NOT NULL DEFAULT '0',
+
    logo_size                    int                      null,
  PRIMARY KEY (`id`)
+
    first_hour_displayed          time                      null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `m_operation`
+
(
--
+
    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 `m_operation` (
+
create table supplier_bill_account_entry
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `content` text NOT NULL,
+
    supplier_bill_id      int unsigned not null,
  `m_component_type_id` int(11) DEFAULT NULL,
+
    account_entry_flow_id int unsigned not null,
  `order_num` int(11) NOT NULL,
+
    primary key (supplier_bill_id, account_entry_flow_id)
  PRIMARY KEY (`id`)
+
);
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create index idx_account_entry_flow_id
-- Table structure for table `map_logger_monitoring`
+
    on supplier_bill_account_entry (account_entry_flow_id);
--
+
  
CREATE TABLE `map_logger_monitoring` (
+
create table supplier_bill_email_parsed
  `record_id` int(10) unsigned NOT NULL,
+
(
  `count_data` int(10) unsigned NOT NULL,
+
    id                    int(10) auto_increment
  PRIMARY KEY (`record_id`)
+
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='link between flight and track';
+
    uid                  int(10)     null,
 +
    email_address        varchar(255) null,
 +
    supplier_bill_file_id int(10)     null
 +
);
  
--
+
create table supplier_bill_file
-- Table structure for table `map_track`
+
(
--
+
    id        int unsigned auto_increment
 +
        primary key,
 +
    file_type varchar(255) null,
 +
    content  longblob    null,
 +
    name      varchar(255) not null
 +
);
  
CREATE TABLE `map_track` (
+
create table supplier_bill_type
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `record_id` int(10) unsigned DEFAULT NULL,
+
    id                 int unsigned auto_increment
  `start_data` int(10) unsigned DEFAULT NULL,
+
        primary key,
  `number` int(10) unsigned DEFAULT NULL,
+
    supplier_account_id int unsigned not null,
  `start_time` datetime DEFAULT NULL,
+
    name                varchar(255) null,
  `latitude_max` double DEFAULT NULL,
+
    account_id          int unsigned not null,
   `longitude_max` double DEFAULT NULL,
+
    vat_account_id      int unsigned null,
  `latitude_min` double DEFAULT NULL,
+
    supplier_budget_id  int unsigned null,
  `longitude_min` double DEFAULT NULL,
+
    account_budget_id   int unsigned null,
  `visible` tinyint(1) unsigned NOT NULL DEFAULT '1',
+
    vat_budget_id      int unsigned null,
  PRIMARY KEY (`id`)
+
    rule                text        null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Tracks split from GDR';
+
    sender_email        varchar(255) null,
 +
    subject            varchar(255) null
 +
);
  
--
+
create table table_trigger
-- Table structure for table `nationality`
+
(
--
+
    id              int unsigned auto_increment
 +
        primary key,
 +
    monitored_table varchar(255) not null,
 +
    trigger_formula varchar(255) not null
 +
);
  
CREATE TABLE `nationality` (
+
create table tank
  `code` char(2) NOT NULL DEFAULT '',
+
(
   `label` varchar(255) NOT NULL,
+
    id                int unsigned auto_increment
  PRIMARY KEY (`code`)
+
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `parameter`
+
(
--
+
    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 `parameter` (
+
create table track_record_data
   `code` varchar(255) NOT NULL,
+
(
  `key_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    id            int unsigned auto_increment
  `enabled` tinyint(1) unsigned DEFAULT '0',
+
        primary key,
  `int_value` int(10) unsigned DEFAULT '0',
+
    record_id    int unsigned        not null,
  `char_value` text,
+
    record_date   datetime            null,
  PRIMARY KEY (`code`,`key_id`)
+
    longitude    double              null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='APPLICATION PARAMETERS';
+
    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
-- Table structure for table `payment_distribution`
+
    on track_record_data (record_id);
--
+
  
CREATE TABLE `payment_distribution` (
+
create table track_resource
  `payment_id` int(10) unsigned NOT NULL DEFAULT '0',
+
(
   `account_club_id` int(10) unsigned DEFAULT NULL,
+
    id   int unsigned auto_increment
  `person_delivery` tinyint(1) unsigned DEFAULT '0',
+
        primary key,
  `member_budget_id` int(10) unsigned DEFAULT NULL,
+
    name varchar(255) not null
  `treasury_budget_id` int(10) unsigned DEFAULT NULL,
+
);
  PRIMARY KEY (`payment_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='payment distribution description';
+
  
--
+
create table uncomp_flight_type
-- Table structure for table `payment_summary_file`
+
(
--
+
    id1 int unsigned null,
 +
    id2 int unsigned null
 +
)
 +
    comment 'List of sales';
  
CREATE TABLE `payment_summary_file` (
+
create table validity_type
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `file_type` varchar(255) DEFAULT NULL,
+
    id                       int unsigned auto_increment
  `content` longblob,
+
        primary key,
  `record_date` datetime DEFAULT NULL,
+
    name                    varchar(255)                   not null,
  `total_entry` int(10) unsigned DEFAULT NULL,
+
    time_limitation          tinyint(1)          default 0  not null,
  `total_amount` decimal(15,5) unsigned DEFAULT NULL,
+
    ident_value_enable      tinyint(1)          default 0  not null,
  `payment_type_id` int(10) unsigned DEFAULT NULL,
+
    grant_date_enable        tinyint(1)         default 0  not null,
  PRIMARY KEY (`id`)
+
    mandatory                tinyint(1)          default 0  not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `payment_type`
+
(
--
+
    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 `payment_type` (
+
create table validity_type_page
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `name` text,
+
    id               int unsigned auto_increment
  `text_field_label` text,
+
        primary key,
  `only_admin` tinyint(1) unsigned NOT NULL DEFAULT '0',
+
    validity_type_id int unsigned        not null,
  `order_num` int(11) DEFAULT NULL,
+
    page_index      int unsigned        not null,
  `pos_key_id` int(10) unsigned DEFAULT NULL,
+
    label            varchar(40)        not null,
  PRIMARY KEY (`id`)
+
    is_mandatory    tinyint(1) unsigned not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='type of payment description';
+
    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
-- Table structure for table `person`
+
(
--
+
    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 `person` (
+
create table validity_2_validity_page
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `name` varchar(255) NOT NULL,
+
    validity_id      int unsigned not null,
  `hash_password` varchar(255) NOT NULL,
+
    validity_page_id int unsigned not null,
  `first_name` varchar(255) DEFAULT NULL,
+
    primary key (validity_id, validity_page_id),
  `last_name` varchar(255) DEFAULT NULL,
+
    constraint validity_2_validity_page_validity_id_fk
  `profile` bigint(20) unsigned DEFAULT NULL,
+
        foreign key (validity_id) references validity (id),
  `view_type` int(10) unsigned DEFAULT NULL,
+
    constraint validity_2_validity_page_validity_page_id_fk
  `view_width` tinyint(3) unsigned NOT NULL DEFAULT '12',
+
        foreign key (validity_page_id) references validity_page (id)
  `view_height` tinyint(4) unsigned NOT NULL DEFAULT '30',
+
);
  `email` varchar(255) DEFAULT NULL,
+
  `timezone` varchar(255) DEFAULT NULL,
+
  `address` varchar(255) DEFAULT NULL,
+
  `zipcode` varchar(255) DEFAULT NULL,
+
  `city` varchar(255) DEFAULT NULL,
+
  `state` varchar(255) DEFAULT NULL,
+
  `country` varchar(255) DEFAULT NULL,
+
  `home_phone` varchar(255) DEFAULT NULL,
+
  `work_phone` varchar(255) DEFAULT NULL,
+
  `cell_phone` varchar(255) DEFAULT NULL,
+
  `lang` varchar(255) DEFAULT NULL,
+
  `notification` tinyint(3) unsigned DEFAULT NULL,
+
  `activated` tinyint(1) unsigned DEFAULT '1',
+
  `birthdate` datetime DEFAULT '0000-00-00 00:00:00',
+
  `sex` tinyint(1) unsigned NOT NULL DEFAULT '0',
+
  `nationality` char(2) DEFAULT NULL,
+
  `total_flight_time` int(10) unsigned DEFAULT '0',
+
  `date_total_flight_time` datetime DEFAULT NULL,
+
  `aircrafts_viewed` varchar(255) DEFAULT NULL,
+
  `inst_viewed` varchar(255) DEFAULT NULL,
+
  PRIMARY KEY (`id`),
+
  UNIQUE KEY `idx_name` (`name`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='used for authentication';
+
  
--
+
create table variable
-- Table structure for table `person_awaiting_activation`
+
(
--
+
    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 `person_awaiting_activation` (
+
create table variable_value
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `login` varchar(255) NOT NULL,
+
    id           int auto_increment
  `ip` varchar(255) NOT NULL,
+
        primary key,
  `code` varchar(255) NOT NULL,
+
    variable_id  int            not null,
  `category` varchar(255) NOT NULL,
+
    assign_value decimal(15, 2) not null,
   `expiration_date` datetime DEFAULT NULL,
+
    start_date   datetime       not null
  `used` tinyint(1) unsigned DEFAULT NULL,
+
);
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create table web_feed
-- Table structure for table `product`
+
(
--
+
    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 `product` (
+
create table web_feed_channel
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
(
  `label` varchar(255) NOT NULL,
+
    id   int unsigned auto_increment
  `unit` varchar(255) NOT NULL,
+
        primary key,
  `free_sale` tinyint(1) NOT NULL DEFAULT '0',
+
    label varchar(255) not null
  `locked` tinyint(1) unsigned DEFAULT '0',
+
);
  `variable_id` int(10) unsigned DEFAULT NULL,
+
  `sale_type` tinyint(1) unsigned DEFAULT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create definer = overallCustomer@localhost view eligible_pilot_for_first_flight as
-- Table structure for table `profile`
+
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 TABLE `profile` (
+
create definer = overallCustomer@localhost view eligible_resource_for_first_flight as
  `id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
select `of40_aeroclublys`.`resource`.`id` AS `resource_id`
  `name` varchar(255) DEFAULT NULL,
+
from ((`of40_aeroclublys`.`resource` join `of40_aeroclublys`.`business_field_content` on ((
  `permits` int(10) unsigned DEFAULT NULL,
+
        `of40_aeroclublys`.`business_field_content`.`category_id` = `of40_aeroclublys`.`resource`.`id`)))
  `permits2` int(10) unsigned NOT NULL DEFAULT '0',
+
        join `of40_aeroclublys`.`business_field` on ((`of40_aeroclublys`.`business_field`.`id` =
  `permits3` int(10) unsigned NOT NULL DEFAULT '0',
+
                                                      `of40_aeroclublys`.`business_field_content`.`business_field_id`)))
  `pictogram` int(10) unsigned DEFAULT NULL,
+
where ((`of40_aeroclublys`.`resource`.`activated` = 1) and
  `default_status_id` int(10) unsigned DEFAULT NULL,
+
      (`of40_aeroclublys`.`business_field`.`variable` = 'canResourcePerformFirstFlights') and
  PRIMARY KEY (`id`)
+
      (`of40_aeroclublys`.`business_field_content`.`content` = '1') and
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
      (`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
-- Table structure for table `profile_accounting_notification`
+
    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;
  
CREATE TABLE `profile_accounting_notification` (
+
    SET rlongitude1 = RADIANS(longitude1);
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
    SET rlatitude1 = RADIANS(latitude1);
  `accounting_id` int(10) unsigned NOT NULL DEFAULT '0',
+
    SET rlongitude2 = RADIANS(longitude2);
  PRIMARY KEY (`profile_id`,`accounting_id`)
+
    SET rlatitude2 = RADIANS(latitude2);
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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
-- Table structure for table `profile_extra_field_join`
+
    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);
  
CREATE TABLE `profile_extra_field_join` (
+
    SELECT IF(replacedString <> '0.00' AND replacedString <> '0', TRIM(TRAILING '0' FROM replacedString),
  `profile_id` bigint(20) unsigned NOT NULL,
+
              replacedString)
  `extra_field_id` int(10) unsigned NOT NULL,
+
    INTO replacedString;
  PRIMARY KEY (`profile_id`,`extra_field_id`)
+
    SELECT TRIM(TRAILING '.' FROM replacedString) INTO replacedString;
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    SELECT REPLACE(replacedString, '.', IFNULL(@decimalSeparator, '.')) INTO replacedString;
 +
    RETURN replacedString;
 +
END;
  
--
+
create
-- Table structure for table `profile_profile_view`
+
    definer = root@localhost function isBalancedAccountEntryFlow(flowId int) returns int
--
+
BEGIN
 +
    DECLARE isBalanced INT;
  
CREATE TABLE `profile_profile_view` (
+
    SELECT IF(SUM(debit) <> SUM(credit), 0, 1)
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
    into isBalanced
  `viewable_profile_id` bigint(20) unsigned NOT NULL,
+
    FROM account_entry
  PRIMARY KEY (`profile_id`,`viewable_profile_id`)
+
    WHERE flow_id = flowId
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    GROUP BY flow_id;
  
--
+
    RETURN isBalanced;
-- Table structure for table `profile_required_account_type`
+
END;
--
+
  
CREATE TABLE `profile_required_account_type` (
+
create
  `profile_id` bigint(20) unsigned NOT NULL,
+
    definer = root@localhost function nearestPoint(latRef double, longRef double, distanceMax int) returns varchar(6)
  `account_type_id` int(10) unsigned NOT NULL,
+
BEGIN
  PRIMARY KEY (`profile_id`,`account_type_id`)
+
    DECLARE rlongitude1 DOUBLE;
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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);
-- Table structure for table `profile_resource_type_place`
+
    SET rlongitude1 = longRef + a / cos(latRef);
--
+
    SET rlongitude2 = longRef - a / cos(latRef);
 +
    SET rlatitude1 = latRef + a;
 +
    SET rlatitude2 = latRef - a;
  
CREATE TABLE `profile_resource_type_place` (
+
    SELECT loc1.icao_name,
  `profile_id` bigint(20) unsigned NOT NULL,
+
          distanceBetween2Point(
  `resource_type_id` int(10) unsigned NOT NULL,
+
                  (SELECT loc2.latitude FROM location AS loc2 WHERE loc2.icao_name = loc1.icao_name),
  `place_num` int(10) unsigned NOT NULL,
+
                  (SELECT loc3.longitude FROM location AS loc3 WHERE loc3.icao_name = loc1.icao_name),
  PRIMARY KEY (`profile_id`,`resource_type_id`,`place_num`)
+
                  latRef,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
                  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;
-- Table structure for table `profile_resource_type_view`
+
END;
--
+
  
CREATE TABLE `profile_resource_type_view` (
+
create
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
    definer = overallCustomer@localhost function sexa2HoursHundredths(sexacentimal int) returns varchar(255)
  `viewable_resource_type_id` int(10) unsigned NOT NULL,
+
BEGIN
  PRIMARY KEY (`profile_id`,`viewable_resource_type_id`)
+
    DECLARE convertedValue VARCHAR(255);
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    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(
-- Table structure for table `profile_validity_type_join`
+
                  roundedHours, decimalSeparator, LPAD(FLOOR(remainingTime / 6), 2, '0')
--
+
              )
 +
    INTO convertedValue;
  
CREATE TABLE `profile_validity_type_join` (
+
    RETURN convertedValue;
  `profile_id` bigint(20) unsigned NOT NULL,
+
END;
  `validity_type_id` int(10) unsigned NOT NULL,
+
  PRIMARY KEY (`profile_id`,`validity_type_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create
-- Table structure for table `profile_validity_type_notification`
+
    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);
  
CREATE TABLE `profile_validity_type_notification` (
+
    SELECT CONCAT(
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
+
                  roundedHours, ':', LPAD(FLOOR(remainingTime / 10), 2, '0')
  `validity_type_id` int(10) unsigned NOT NULL,
+
              )
  PRIMARY KEY (`profile_id`,`validity_type_id`)
+
    INTO convertedValue;
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
    RETURN convertedValue;
-- Table structure for table `psp_return`
+
END;
--
+
  
CREATE TABLE `psp_return` (
+
create
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
    definer = root@localhost function stripChars(word varchar(255)) returns varchar(255)
  `psp_transaction_id` int(10) unsigned DEFAULT NULL,
+
BEGIN
  `bank_answer` text,
+
    DECLARE stripWord VARCHAR(255);
  `bank_misc` text,
+
    SET stripWord = word;
  PRIMARY KEY (`id`)
+
    SELECT IF(stripWord REGEXP '[-]', REPLACE(stripWord, '-', ''), stripWord) INTO stripWord;
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    SELECT IF(stripWord REGEXP '[ ]', REPLACE(stripWord, ' ', ''), stripWord) INTO stripWord;
 +
    RETURN stripWord;
 +
END;
  
--
+
create
-- Table structure for table `psp_transaction`
+
    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);
  
CREATE TABLE `psp_transaction` (
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `credit_account_id` int(10) unsigned NOT NULL,
 
  `debit_account_id` int(10) unsigned NOT NULL,
 
  `transaction_date` datetime NOT NULL,
 
  `amount` float NOT NULL,
 
  `description` varchar(255) DEFAULT NULL,
 
  `payment_type_id` int(10) unsigned NOT NULL,
 
  `state` int(10) unsigned NOT NULL DEFAULT '0',
 
  `token` text,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
  
--
+
    SELECT balance_date, IFNULL(debit, 0.00), IFNULL(credit, 0.00)
-- Table structure for table `psp_transaction_account_entry`
+
    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;
  
CREATE TABLE `psp_transaction_account_entry` (
 
  `psp_transaction_id` int(10) unsigned NOT NULL DEFAULT '0',
 
  `account_entry_flow_id` int(10) unsigned NOT NULL DEFAULT '0',
 
  PRIMARY KEY (`psp_transaction_id`,`account_entry_flow_id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
  
--
+
    IF (SELECT FOUND_ROWS()) = 0 THEN
-- Table structure for table `regular_presence_inst_date`
+
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit, balanceCredit;
--
+
    END IF;
  
CREATE TABLE `regular_presence_inst_date` (
+
    SELECT IFNULL(SUM(debit), 0.00) + balanceDebit, IFNULL(SUM(credit), 0.00) + balanceCredit
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
    INTO totalDebit, totalCredit
  `person_id` int(10) unsigned DEFAULT NULL,
+
    FROM account_entry
  `start_day` tinyint(3) unsigned DEFAULT NULL,
+
    WHERE account_id = accountId
  `end_day` tinyint(3) unsigned DEFAULT NULL,
+
      AND account_date >= CAST(balanceDate AS DATETIME)
  `start_hour` time DEFAULT NULL,
+
      AND account_date < CAST(endDate AS DATETIME);
  `end_hour` time DEFAULT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
    SELECT (totalCredit - totalDebit) INTO totalBalance;
-- Table structure for table `resource`
+
--
+
  
CREATE TABLE `resource` (
+
    RETURN totalBalance;
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
END;
  `name` varchar(255) DEFAULT NULL,
+
  `resource_type_id` int(10) unsigned DEFAULT NULL,
+
  `comments` varchar(255) DEFAULT NULL,
+
  `order_num` int(11) DEFAULT NULL,
+
  `activated` tinyint(1) unsigned NOT NULL DEFAULT '1',
+
  `bookable` int(10) unsigned DEFAULT '1',
+
  `physical` int(10) unsigned DEFAULT '1',
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create
-- Table structure for table `resource_exceptional_availability`
+
    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);
  
CREATE TABLE `resource_exceptional_availability` (
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `resource_id` int(10) unsigned DEFAULT NULL,
 
  `start_date` datetime DEFAULT NULL,
 
  `end_date` datetime DEFAULT NULL,
 
  `presence` tinyint(1) unsigned DEFAULT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
  
--
+
    SELECT balance_date, IFNULL(credit, 0.00)
-- Table structure for table `resource_regular_availability`
+
    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;
  
CREATE TABLE `resource_regular_availability` (
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `resource_id` int(10) unsigned DEFAULT NULL,
 
  `start_day` tinyint(1) unsigned DEFAULT NULL,
 
  `end_day` tinyint(1) unsigned DEFAULT NULL,
 
  `start_hour` time DEFAULT NULL,
 
  `end_hour` time DEFAULT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
  
--
+
    IF (SELECT FOUND_ROWS()) = 0 THEN
-- Table structure for table `resource_type`
+
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceCredit;
--
+
    END IF;
  
CREATE TABLE `resource_type` (
+
    SELECT IFNULL(SUM(credit), 0.00) + balanceCredit
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
    INTO totalCredit
  `name` varchar(255) DEFAULT NULL,
+
    FROM account_entry
  `category` int(10) unsigned DEFAULT NULL,
+
    WHERE account_id = accountId
  `seats_available` int(11) DEFAULT '-1',
+
      AND account_date >= CAST(balanceDate AS DATETIME)
  `comments` varchar(255) DEFAULT NULL,
+
      AND account_date < CAST(endDate AS DATETIME);
  `order_num` int(11) DEFAULT NULL,
+
  `activated` tinyint(1) unsigned NOT NULL DEFAULT '1',
+
  `max_booking_duration` int(10) DEFAULT '-1',
+
  `pictogram` int(10) unsigned DEFAULT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
    RETURN totalCredit;
-- Table structure for table `resource_type_place_tag`
+
END;
--
+
  
CREATE TABLE `resource_type_place_tag` (
+
create
  `resource_type_id` int(10) unsigned NOT NULL,
+
    definer = overallCustomer@localhost function sumAccountEntryDebit(accountId int, endDate datetime) returns decimal(15, 2)
  `place_num` tinyint(1) unsigned NOT NULL,
+
BEGIN
  `place_tag` varchar(255) DEFAULT NULL,
+
    DECLARE balanceDate DATETIME;
  `place_quantity` int(10) DEFAULT '-1',
+
    DECLARE balanceDebit DECIMAL(15, 2);
  PRIMARY KEY (`resource_type_id`,`place_num`)
+
    DECLARE totalDebit DECIMAL(15, 2);
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
 
-- Table structure for table `sale_2_stock`
 
--
 
  
CREATE TABLE `sale_2_stock` (
+
    SELECT balance_date, IFNULL(debit, 0.00)
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
    INTO balanceDate, balanceDebit
  `product_id` int(10) unsigned NOT NULL,
+
    FROM balance_date
  `stock_id` int(10) unsigned NOT NULL,
+
            LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId)
  `stock_variation_qty_per_sale` float NOT NULL DEFAULT '0',
+
    WHERE balance_date.balance_date < endDate
  PRIMARY KEY (`id`)
+
    ORDER BY balance_date DESC
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
    LIMIT 1;
  
--
 
-- Table structure for table `sale_2_validity_type`
 
--
 
  
CREATE TABLE `sale_2_validity_type` (
+
    IF (SELECT FOUND_ROWS()) = 0 THEN
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit;
  `validity_type_id` int(10) unsigned NOT NULL,
+
    END IF;
  `new_formula` varchar(255) DEFAULT NULL,
+
  `update_formula` varchar(255) DEFAULT NULL,
+
  `product_id` int(10) unsigned NOT NULL,
+
  PRIMARY KEY (`id`),
+
  UNIQUE KEY `validity_type_id` (`validity_type_id`),
+
  UNIQUE KEY `validity_type_id_2` (`validity_type_id`),
+
  KEY `idx_product_id` (`product_id`),
+
  KEY `idx_product` (`product_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
    SELECT IFNULL(SUM(debit), 0.00) + balanceDebit
-- Table structure for table `sale_pricing`
+
    INTO totalDebit
--
+
    FROM account_entry
 +
    WHERE account_id = accountId
 +
      AND account_date >= CAST(balanceDate AS DATETIME)
 +
      AND account_date < CAST(endDate AS DATETIME);
  
CREATE TABLE `sale_pricing` (
+
    RETURN totalDebit;
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
END;
  `label` varchar(255) DEFAULT NULL,
+
  `price_formula` text,
+
  `debit_account_id` int(10) unsigned DEFAULT NULL,
+
  `credit_account_id` int(10) unsigned DEFAULT NULL,
+
  `debit_account_type` tinyint(1) unsigned DEFAULT '0',
+
  `credit_account_type` tinyint(1) unsigned DEFAULT '0',
+
  `credit_budget_id` int(10) unsigned DEFAULT NULL,
+
  `debit_budget_id` int(10) unsigned DEFAULT NULL,
+
  `order_num` int(11) DEFAULT NULL,
+
  `variable_formula` varchar(255) DEFAULT NULL,
+
  `debit_bill_num` int(10) unsigned DEFAULT NULL,
+
  `credit_bill_num` int(10) unsigned DEFAULT NULL,
+
  `qty_formula` text,
+
  `unit_price_formula` text,
+
  `added_product_id` int(10) unsigned DEFAULT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
create
-- Table structure for table `sale_pricing_product`
+
    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);
  
CREATE TABLE `sale_pricing_product` (
 
  `sale_pricing_id` int(10) unsigned NOT NULL DEFAULT '0',
 
  `product_id` int(10) unsigned NOT NULL DEFAULT '0',
 
  PRIMARY KEY (`sale_pricing_id`,`product_id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
  
--
+
    SELECT balance_date, IFNULL(debit, 0.00), IFNULL(credit, 0.00)
-- Table structure for table `sale_pricing_profile`
+
    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;
  
CREATE TABLE `sale_pricing_profile` (
 
  `sale_pricing_id` int(10) unsigned NOT NULL,
 
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
 
  PRIMARY KEY (`sale_pricing_id`,`profile_id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
  
--
+
    IF (SELECT FOUND_ROWS()) = 0 THEN
-- Table structure for table `sale_trigger`
+
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit, balanceCredit;
--
+
    END IF;
  
CREATE TABLE `sale_trigger` (
+
    SELECT IFNULL(SUM(debit), 0.00) + balanceDebit, IFNULL(SUM(credit), 0.00) + balanceCredit
  `id` int(10) NOT NULL DEFAULT '0',
+
    INTO totalDebit, totalCredit
  `name` varchar(255) DEFAULT NULL,
+
    FROM account_entry
  `event` varchar(255) DEFAULT NULL,
+
    WHERE account_id = accountId
  `locked` tinyint(1) unsigned NOT NULL DEFAULT '0',
+
      AND account_date >= CAST(balanceDate AS DATETIME)
  PRIMARY KEY (`id`)
+
      AND account_date < CAST(endDate AS DATETIME)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
      AND validated = 1;
  
--
+
    SELECT (totalCredit - totalDebit) INTO totalBalance;
-- Table structure for table `spreadsheet_parameter`
+
--
+
  
CREATE TABLE `spreadsheet_parameter` (
+
    RETURN totalBalance;
  `file_number` int(10) NOT NULL,
+
END;</sql>
  `spreadsheet_key` varchar(255) NOT NULL,
+
  `spreadsheet_value` varchar(255) NOT NULL,
+
  `file_name` varchar(255) DEFAULT NULL,
+
  `google_url` varchar(255) NOT NULL,
+
  `file_type` int(10) unsigned DEFAULT NULL,
+
  PRIMARY KEY (`file_number`,`spreadsheet_key`,`spreadsheet_value`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
=Description=
-- Table structure for table `statistic`
+
--
+
  
CREATE TABLE `statistic` (
+
==Table booking==
  `name` varchar(255) NOT NULL,
+
  `of_version` tinyint(3) NOT NULL DEFAULT '0',
+
  `value` decimal(15,2) DEFAULT '0.00',
+
  PRIMARY KEY (`name`,`of_version`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
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 structure for table `status`
+
--
+
  
CREATE TABLE `status` (
+
==Table flight==
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `abbrev` varchar(255) DEFAULT NULL,
+
  `name` varchar(255) DEFAULT NULL,
+
  `pictogram` int(10) unsigned DEFAULT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of functions for pilot';
+
  
--
+
*airborne :
-- Table structure for table `stock`
+
**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.
  
CREATE TABLE `stock` (
+
==Champs cachés==
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
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é :
  `label` varchar(255) NOT NULL,
+
*Table journal : Tous les champs
  `stock_type_id` int(10) unsigned NOT NULL,
+
*Table log : Tous les champs
  PRIMARY KEY (`id`)
+
*Table parameter : Tous les champs
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
*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
  
--
+
Exemple de requêtes non autorisées :
-- Table structure for table `stock_level`
+
<sql>SELECT * FROM structure;
--
+
  
CREATE TABLE `stock_level` (
+
SELECT hash_password FROM person;
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `stock_id` int(10) unsigned NOT NULL,
+
  `qty` float NOT NULL DEFAULT '0',
+
  `stock_date` datetime DEFAULT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
  
--
+
SELECT person.id, validity.*
-- Table structure for table `stock_type`
+
FROM person
--
+
LEFT JOIN validity ON (person.id=validity.person_id);</sql>
 
+
CREATE TABLE `stock_type` (
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `label` varchar(255) NOT NULL,
+
  `unit` varchar(255) NOT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
 
+
--
+
-- Table structure for table `stock_variation`
+
--
+
 
+
CREATE TABLE `stock_variation` (
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `stock_id` int(10) unsigned NOT NULL,
+
  `qty` float NOT NULL DEFAULT '0',
+
  `variation_date` datetime DEFAULT NULL,
+
  `validated` tinyint(1) unsigned DEFAULT '0',
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
 
+
--
+
-- Table structure for table `stock_variation_account_entry`
+
--
+
 
+
CREATE TABLE `stock_variation_account_entry` (
+
  `product_id` int(10) unsigned NOT NULL DEFAULT '0',
+
  `stock_variation_id` int(10) unsigned NOT NULL,
+
  `account_entry_flow_id` int(10) unsigned NOT NULL,
+
  `person_id` int(10) unsigned NOT NULL,
+
  PRIMARY KEY (`product_id`,`stock_variation_id`,`account_entry_flow_id`),
+
  KEY `idx_account_entry_flow_id` (`account_entry_flow_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
 
+
--
+
-- Table structure for table `supplier_bill`
+
--
+
 
+
CREATE TABLE `supplier_bill` (
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `supplier_bill_type_id` int(10) unsigned NOT NULL,
+
  `bill_date` date DEFAULT NULL,
+
  `description` varchar(255) DEFAULT NULL,
+
  `ordinal` int(10) unsigned NOT NULL,
+
  `validated` tinyint(1) DEFAULT '0',
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
 
+
--
+
-- Table structure for table `supplier_bill_account_entry`
+
--
+
 
+
CREATE TABLE `supplier_bill_account_entry` (
+
  `supplier_bill_id` int(10) unsigned NOT NULL,
+
  `account_entry_flow_id` int(10) unsigned NOT NULL,
+
  PRIMARY KEY (`supplier_bill_id`,`account_entry_flow_id`),
+
  KEY `idx_account_entry_flow_id` (`account_entry_flow_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
 
+
--
+
-- Table structure for table `supplier_bill_type`
+
--
+
 
+
CREATE TABLE `supplier_bill_type` (
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `supplier_account_id` int(10) unsigned NOT NULL,
+
  `name` varchar(255) DEFAULT NULL,
+
  `account_id` int(10) unsigned NOT NULL,
+
  `vat_account_id` int(10) unsigned DEFAULT NULL,
+
  `supplier_budget_id` int(10) unsigned DEFAULT NULL,
+
  `account_budget_id` int(10) unsigned DEFAULT NULL,
+
  `vat_budget_id` int(10) unsigned DEFAULT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
 
+
--
+
-- Table structure for table `table_trigger`
+
--
+
 
+
CREATE TABLE `table_trigger` (
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `monitored_table` varchar(255) NOT NULL,
+
  `trigger_formula` varchar(255) NOT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
 
+
--
+
-- Table structure for table `tank`
+
--
+
 
+
CREATE TABLE `tank` (
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `aircraft_type_id` int(10) unsigned NOT NULL,
+
  `tank_type_id` int(10) unsigned NOT NULL,
+
  `unit_id` int(10) unsigned NOT NULL,
+
  `label` varchar(255) NOT NULL,
+
  `max_quantity` decimal(15,2) NOT NULL DEFAULT '-1.00',
+
  `unlimited_quantity` int(10) unsigned DEFAULT '1',
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
 
+
--
+
-- Table structure for table `track_record`
+
--
+
 
+
CREATE TABLE `track_record` (
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `in_progress` tinyint(1) unsigned DEFAULT NULL,
+
  `start_date` datetime DEFAULT '0000-00-00 00:00:00',
+
  `activated` tinyint(1) unsigned DEFAULT NULL,
+
  `datechsys_track_id` int(10) unsigned DEFAULT NULL,
+
  `resource_cat` int(10) unsigned NOT NULL,
+
  `resource_id` int(10) unsigned NOT NULL,
+
  `additional_information` int(10) unsigned DEFAULT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
 
+
--
+
-- Table structure for table `track_record_data`
+
--
+
 
+
CREATE TABLE `track_record_data` (
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `record_id` int(10) unsigned NOT NULL,
+
  `record_date` datetime DEFAULT NULL,
+
  `longitude` double DEFAULT NULL,
+
  `latitude` double DEFAULT NULL,
+
  `altitude` int(7) DEFAULT NULL,
+
  `speed` double DEFAULT NULL,
+
  `track` int(3) unsigned DEFAULT NULL,
+
  `acc_x` int(6) DEFAULT NULL,
+
  `acc_y` int(6) DEFAULT NULL,
+
  `acc_z` int(6) DEFAULT NULL,
+
  `pressure` int(5) unsigned DEFAULT NULL,
+
  `battery_level` int(4) unsigned DEFAULT NULL,
+
  `gps_fix` tinyint(1) unsigned DEFAULT NULL,
+
  `receive_date` datetime DEFAULT NULL,
+
  PRIMARY KEY (`id`),
+
  KEY `idx_record_id` (`record_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
 
+
--
+
-- Table structure for table `track_resource`
+
--
+
 
+
CREATE TABLE `track_resource` (
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `name` varchar(255) NOT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
 
+
--
+
-- Table structure for table `uncomp_flight_type`
+
--
+
 
+
CREATE TABLE `uncomp_flight_type` (
+
  `id1` int(10) unsigned DEFAULT NULL,
+
  `id2` int(10) unsigned DEFAULT NULL
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of sales';
+
 
+
--
+
-- Table structure for table `validity`
+
--
+
 
+
CREATE TABLE `validity` (
+
  `person_id` int(10) unsigned NOT NULL,
+
  `validity_type_id` int(10) unsigned NOT NULL,
+
  `expire_date` date DEFAULT NULL,
+
  `no_alert` tinyint(1) unsigned NOT NULL DEFAULT '0',
+
  `ident_value` varchar(255) DEFAULT NULL,
+
  `grant_date` date DEFAULT NULL,
+
  PRIMARY KEY (`person_id`,`validity_type_id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='QUALIFICATIONS OF EACH MEMBER';
+
 
+
--
+
-- Table structure for table `validity_type`
+
--
+
 
+
CREATE TABLE `validity_type` (
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `name` varchar(255) NOT NULL,
+
  `time_limitation` tinyint(1) NOT NULL DEFAULT '0',
+
  `ident_value_enable` tinyint(1) NOT NULL DEFAULT '0',
+
  `grant_date_enable` tinyint(1) NOT NULL DEFAULT '0',
+
  `mandatory` tinyint(1) NOT NULL DEFAULT '0',
+
  `experience_formula` varchar(255) DEFAULT NULL,
+
  `alert_on_login` int(11) DEFAULT '-2',
+
  `mandatory_access_control` tinyint(1) unsigned DEFAULT '0',
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='QUALIFICATIONS LIST';
+
 
+
--
+
-- Table structure for table `variable`
+
--
+
 
+
CREATE TABLE `variable` (
+
  `id` int(11) NOT NULL AUTO_INCREMENT,
+
  `variable` varchar(255) NOT NULL,
+
  `label` varchar(255) NOT NULL,
+
  `category` tinyint(1) DEFAULT NULL,
+
  `value_type` varchar(255) DEFAULT NULL,
+
  `order_num` int(11) NOT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
 
+
--
+
-- Table structure for table `variable_value`
+
--
+
 
+
CREATE TABLE `variable_value` (
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `variable_id` int(11) NOT NULL,
+
  `assign_value` decimal(15,2) NOT NULL,
+
  `start_date` datetime NOT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
 
+
--
+
-- Table structure for table `web_feed`
+
--
+
 
+
CREATE TABLE `web_feed` (
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `channel_id` int(10) unsigned NOT NULL,
+
  `publication_date` datetime NOT NULL,
+
  `title` varchar(255) DEFAULT NULL,
+
  `content` text,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
 
+
--
+
-- Table structure for table `web_feed_channel`
+
--
+
 
+
CREATE TABLE `web_feed_channel` (
+
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+
  `label` varchar(255) NOT NULL,
+
  PRIMARY KEY (`id`)
+
) ENGINE=InnoDB DEFAULT CHARSET=latin1;</sql>
+

Version du 21 novembre 2020 à 13:05

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 auto_increment
        primary key,
    flow_id             int                            null,
    account_date        datetime                       null,
    account_id          int                            null,
    credit              decimal(15, 5) default 0.00000 null,
    debit               decimal(15, 5) default 0.00000 null,
    payment_type        int unsigned                   null,
    payment_description text                           null,
    person_delivery_id  int unsigned                   null,
    comments            text                           null,
    validated           int(1)         default 0       null,
    exported            int(1)         default 0       null,
    registration_date   datetime                       null,
    budget_id           int unsigned                   null,
    product_id          int unsigned                   null,
    signature           varchar(56)                    null,
    signature_date      bigint(13) unsigned            null,
    lettering           int                            null,
    lettering_date      datetime                       null
)
    comment 'List of account entries';
 
create index idx_account_date
    on account_entry (account_date);
 
create index idx_flow_id
    on account_entry (flow_id);
 
create index idx_signature_date
    on account_entry (signature_date);
 
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_training
(
    activity_id       int unsigned default 0 not null,
    theme_id          int unsigned default 0 not null,
    student_id        int(10)                null,
    level_reached     int unsigned           null,
    comment           varchar(255)           null,
    validated         tinyint(1)   default 0 not null,
    checker_person_id int(10)                null,
    checking_date     datetime               null,
    checking_sentence varchar(255)           null,
    primary key (activity_id, theme_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 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 unsigned auto_increment
        primary key,
    variable                 varchar(255)                   null,
    label                    varchar(255)                   not null,
    value_type               varchar(40)                    not null,
    category                 varchar(255)                   not null,
    order_num                int                            not null,
    dsn                      varchar(20) default 'customer' null,
    compulsory_fill          tinyint(1)  default 0          null,
    linked_category          varchar(255)                   null,
    linked_business_field_id int unsigned                   null,
    linked_field_name        varchar(255)                   null,
    max_display              int(10)     default -1         null,
    formula                  text                           null,
    default_value            text                           null,
    placeholder              text                           null,
    access_level_required    tinyint     default 0          not null,
    constraint variable
        unique (variable)
)
    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 unsigned auto_increment
        primary key,
    flight_id int(10)    not null,
    track     mediumblob not null
);
 
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_distribution
(
    payment_id         int unsigned        default 0 not null
        primary key,
    account_club_id    int unsigned                  null,
    person_delivery    tinyint(1) unsigned default 0 null,
    member_budget_id   int unsigned                  null,
    treasury_budget_id int unsigned                  null
)
    comment 'payment distribution description';
 
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 unsigned auto_increment
        primary key,
    name             text                          null,
    text_field_label text                          null,
    only_admin       tinyint(1) unsigned default 0 not null,
    order_num        int                           null,
    pos_key_id       int unsigned                  null
)
    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 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

Exemple de requêtes non autorisées :

SELECT * FROM structure;
 
SELECT hash_password FROM person;
 
SELECT person.id, validity.*
FROM person
LEFT JOIN validity ON (person.id=validity.person_id);