Structure de la base de données: Difference between revisions

From Documentation de la solution web de gestion OpenFlyers
Jump to navigation Jump to search
imported>Csaccoccio
No edit summary
imported>Csaccoccio
Line 1: Line 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) 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,
  `group_sales` tinyint(1) NOT NULL DEFAULT '0',
  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,
  `registration_date` datetime DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `credit` decimal(15,5) DEFAULT '0.00000',
  `debit` decimal(15,5) DEFAULT '0.00000',
  `payment_type` int(10) unsigned DEFAULT NULL,
  `payment_description` text,
  `person_delivery_id` int(10) unsigned DEFAULT NULL,
  `comments` text,
  `validated` int(1) DEFAULT '0',
  `exported` int(1) DEFAULT '0',
  `budget_id` int(10) unsigned DEFAULT NULL,
  `product_id` int(10) unsigned DEFAULT NULL,
  `signature` varchar(56) DEFAULT NULL,
  `signature_date` bigint(13) unsigned DEFAULT NULL,
  `lettering` int(11) DEFAULT NULL,
  `lettering_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_account_date` (`account_date`),
  KEY `idx_flow_id` (`flow_id`),
  KEY `idx_signature_date` (`signature_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) unsigned 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_training`
(
--
    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_training` (
create table aircraft_type_allowed_status
  `activity_id` int(10) unsigned NOT NULL DEFAULT '0',
(
  `theme_id` int(10) unsigned NOT NULL DEFAULT '0',
    aircraft_type_id int unsigned null,
  `student_id` int(10) DEFAULT NULL,
    place_num        int unsigned null,
  `level_reached` int(10) unsigned DEFAULT NULL,
    status_id        int unsigned null
  `comment` varchar(255) DEFAULT NULL,
)
  `validated` tinyint(1) NOT NULL DEFAULT '0',
    comment 'List of allowed functions for each aircraft type';
  `checker_person_id` int(10) DEFAULT NULL,
  `checking_date` datetime DEFAULT NULL,
  `checking_sentence` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`activity_id`,`theme_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
create table aircraft_type_mandatory_flight_type
-- Table structure for table `activity_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 `activity_type` (
create table aircraft_type_uncomp_flight_type
  `id` bigint(20) unsigned NOT NULL DEFAULT '0',
(
  `name` varchar(255) DEFAULT NULL,
    aircraft_type_id int unsigned    null,
  `order_num` int(11) DEFAULT NULL,
    activity_type_id bigint unsigned null
  `activated` tinyint(1) NOT NULL DEFAULT '1',
)
  `color` int(10) unsigned DEFAULT NULL,
    comment 'List of uncompatible flight type for each aircraft type';
  `training` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='list of flight type';


--
create table aircraft_type_validity_type
-- Table structure for table `aircraft`
(
--
    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` (
create table allocation_rule
  `id` int(10) unsigned NOT NULL DEFAULT '0',
(
  `ref_date` datetime DEFAULT NULL,
    id         int unsigned auto_increment
  `ref_hours` int(11) DEFAULT NULL,
        primary key,
  `inspection_date` datetime DEFAULT NULL,
    title      varchar(255)         not null,
  `inspection_time` int(11) DEFAULT NULL,
    account_id int(10)             not null,
  `tolerance_time` int(11) unsigned DEFAULT '6000',
    keyword    varchar(255)         null,
  `last_counter` int(11) DEFAULT NULL,
    order_num  int(10)             not null,
  `interval_visit` int(11) unsigned DEFAULT '30000',
    amount    tinyint(1)           not null,
  `time_alert1` int(11) DEFAULT '6000',
    mandatory  tinyint(1) default 0 not null
  `time_alert2` int(11) DEFAULT '0',
);
  `time_alert3` int(11) DEFAULT '0',
  `day_alert1` smallint(3) DEFAULT '15',
  `day_alert2` smallint(3) DEFAULT '0',
  `day_alert3` smallint(3) DEFAULT '8',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='ALL AIRCRAFTS OF ALL AIRCLUBS';


--
create table balance
-- Table structure for table `aircraft_type`
(
--
    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` (
create table balance_date
  `id` int(10) unsigned NOT NULL,
(
  `flight_time_formula` varchar(255) DEFAULT NULL,
    id           int unsigned auto_increment
  `counter_state` tinyint(2) NOT NULL DEFAULT '-1',
        primary key,
  `tolerance` int(11) DEFAULT '0',
    balance_date datetime null
  `autonomy` int(11) DEFAULT '5990',
);
  `digit_counter_number` tinyint(1) unsigned NOT NULL DEFAULT '4',
  `true_air_speed` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Types of aircraft';


--
create table booking
-- Table structure for table `aircraft_type_allowed_status`
(
--
    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_allowed_status` (
create table booking_account_entry
  `aircraft_type_id` int(10) unsigned DEFAULT NULL,
(
  `place_num` int(10) unsigned DEFAULT NULL,
    booking_id            int unsigned default 0 not null,
  `status_id` int(10) DEFAULT NULL
    account_entry_flow_id int unsigned default 0 not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of allowed functions for each aircraft type';
    primary key (booking_id, account_entry_flow_id)
);


--
create table booking_activity_type
-- Table structure for table `aircraft_type_mandatory_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_mandatory_flight_type` (
create table booking_changelog
  `aircraft_type_id` int(10) unsigned DEFAULT NULL,
(
  `activity_type_id` bigint(20) unsigned DEFAULT NULL
    id            int unsigned auto_increment
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of compulsory 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_uncomp_flight_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_uncomp_flight_type` (
create table booking_resource
  `aircraft_type_id` int(10) unsigned DEFAULT NULL,
(
  `activity_type_id` bigint(20) unsigned DEFAULT NULL
    booking_id  int unsigned default 0 not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of uncompatible flight type for each aircraft type';
    resource_id int unsigned default 0 not null,
    primary key (booking_id, resource_id)
);


--
create table budget
-- Table structure for table `aircraft_type_validity_type`
(
--
    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 `aircraft_type_validity_type` (
create table budget_group
  `aircraft_type_id` int(10) unsigned NOT NULL DEFAULT '0',
(
  `validity_type_id` int(10) unsigned NOT NULL,
    id        int unsigned auto_increment
  `check_num` int(10) unsigned NOT NULL DEFAULT '0',
        primary key,
  PRIMARY KEY (`aircraft_type_id`,`validity_type_id`,`check_num`)
    name      varchar(255) null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Types of qualif required for each aircraft type';
    order_num int         null
);


--
create table business_field
-- Table structure for table `allocation_rule`
(
--
    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 `allocation_rule` (
create table business_field_activity_type
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `title` varchar(255) NOT NULL,
    business_field_id      int unsigned   default 0 not null,
  `account_id` int(10) NOT NULL,
    activity_type_id        bigint unsigned default 0 not null,
  `keyword` varchar(255) DEFAULT NULL,
    business_field_group_id int unsigned    default 0 not null,
  `order_num` int(10) NOT NULL,
    visibility_type        tinyint(1) unsigned      null,
  `amount` tinyint(1) NOT NULL,
    primary key (business_field_id, activity_type_id)
  `mandatory` tinyint(1) NOT NULL DEFAULT '0',
);
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
create table business_field_content
-- Table structure for table `balance`
(
--
    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 `balance` (
create table business_field_group
  `account_id` int(10) unsigned NOT NULL,
(
  `balance_date_id` int(10) unsigned NOT NULL DEFAULT '0',
    id        int unsigned auto_increment
  `debit` decimal(15,5) DEFAULT '0.00000',
        primary key,
  `credit` decimal(15,5) DEFAULT '0.00000',
    label    varchar(255) null,
  PRIMARY KEY (`account_id`,`balance_date_id`)
    order_num int          null
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
);


--
create table criteria
-- Table structure for table `balance_date`
(
--
    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 `balance_date` (
create table criteria_profile
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `balance_date` datetime DEFAULT NULL,
    criteria_id int unsigned   not null,
  PRIMARY KEY (`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`
(
--
    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` (
create index idx_account_entry_flow_id
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    on customer_bill_entry (account_entry_flow_id);
  `booking_group_id` int(10) unsigned DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='RECORDS ALL THE SLOTS OF ALL AIRCRAFTS OF ALL AIRCLUBS';


--
create index idx_account_entry_id
-- Table structure for table `booking_account_entry`
    on customer_bill_entry (account_entry_id);
--


CREATE TABLE `booking_account_entry` (
create table customer_bill_file
  `booking_id` int(10) unsigned NOT NULL DEFAULT '0',
(
  `account_entry_flow_id` int(10) unsigned NOT NULL DEFAULT '0',
    id        int unsigned auto_increment
   PRIMARY KEY (`booking_id`,`account_entry_flow_id`)
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    file_type varchar(255) null,
    content   longblob    null
)
    comment 'List of customer bill stored file';


--
create table customer_receipt_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,
    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_activity_type` (
create index idx_account_entry_flow_id
  `booking_id` int(10) unsigned NOT NULL DEFAULT '0',
    on customer_receipt_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_receipt_entry (account_entry_id);
--


CREATE TABLE `booking_changelog` (
create table customer_receipt_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;


--
create table default_display
-- Table structure for table `booking_person`
(
--
    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 `booking_person` (
create table default_multi_display
  `booking_id` int(10) unsigned NOT NULL DEFAULT '0',
(
  `person_id` int(10) unsigned NOT NULL DEFAULT '0',
    resource_type char(30)     not null,
   `place_num` int(10) unsigned DEFAULT NULL,
    person_id     int unsigned not null,
  `status_id` int(10) unsigned DEFAULT NULL,
    resource_id   varchar(100) not null,
  PRIMARY KEY (`booking_id`,`person_id`)
    display_value text        null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    primary key (resource_type, person_id, resource_id)
);


--
create table email_sent
-- Table structure for table `booking_resource`
(
--
    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 `booking_resource` (
create table email_sent_recipient
  `booking_id` int(10) unsigned NOT NULL DEFAULT '0',
(
  `resource_id` int(10) unsigned NOT NULL DEFAULT '0',
    id                  bigint unsigned auto_increment
  PRIMARY KEY (`booking_id`,`resource_id`)
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    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
-- Table structure for table `budget`
    on email_sent_recipient (email_sent_id);
--


CREATE TABLE `budget` (
create table exceptionnal_inst_date
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `group_id` int(10) unsigned NOT NULL DEFAULT '0',
    id         int unsigned auto_increment
  `name` varchar(255) DEFAULT NULL,
        primary key,
   `order_num` int(11) NOT NULL,
    person_id  int unsigned       null,
   `export_code` varchar(255) DEFAULT NULL,
    start_date datetime            null,
  PRIMARY KEY (`id`)
    end_date   datetime            null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    presence   tinyint(1) unsigned null
);


--
create table extra_field_profile
-- Table structure for table `budget_group`
(
--
    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 `budget_group` (
create table facebook
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `name` varchar(255) DEFAULT NULL,
    id         int unsigned auto_increment
  `order_num` int(11) NOT NULL,
        primary key,
   PRIMARY KEY (`id`)
    category    int unsigned not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    owner_id    int unsigned not null,
    small      blob        null,
    original    mediumblob   not null,
    label      text        null,
    description text        null
);


--
create table favorite_icao
-- Table structure for table `business_field`
(
--
    icao varchar(6) default '' not null
        primary key
)
    comment 'favorite airfield list';


CREATE TABLE `business_field` (
create table favorite_report
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `variable` varchar(255) DEFAULT NULL,
    report_id int unsigned not null
  `label` varchar(255) DEFAULT NULL,
        primary key
  `value_type` varchar(40) DEFAULT NULL,
);
  `category` varchar(255) DEFAULT NULL,
  `order_num` int(11) DEFAULT NULL,
  `dsn` varchar(20) DEFAULT NULL,
  `compulsory_fill` tinyint(1) DEFAULT '0',
  `linked_category` varchar(255) DEFAULT NULL,
  `linked_business_field_id` int(10) unsigned DEFAULT NULL,
  `linked_field_name` varchar(255) DEFAULT NULL,
  `max_display` int(10) DEFAULT '-1',
  `formula` text,
  `default_value` text,
  `placeholder` text,
  `access_level_required` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `variable` (`variable`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of extra form field';


--
create table favorite_report_business_field
-- Table structure for table `business_field_activity_type`
(
--
    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_activity_type` (
create table favorite_report_profile
  `business_field_id` int(10) unsigned NOT NULL DEFAULT '0',
(
  `activity_type_id` bigint(20) unsigned NOT NULL DEFAULT '0',
    report_id  int unsigned             not null,
  `business_field_group_id` int(10) unsigned NOT NULL DEFAULT '0',
    profile_id bigint unsigned default 0 not null,
  `visibility_type` tinyint(1) unsigned DEFAULT NULL,
    primary key (report_id, profile_id)
  PRIMARY KEY (`business_field_id`,`activity_type_id`)
);
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
create table fhp_aircraft_type
-- Table structure for table `business_field_content`
(
--
    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 `business_field_content` (
create table fhp_flight_type
  `category_id` int(10) unsigned NOT NULL,
(
  `business_field_id` int(10) unsigned NOT NULL DEFAULT '0',
    fhp_id          int(10)             default 0 not null,
  `person_id` int(10) unsigned NOT NULL DEFAULT '0',
    activity_type_id bigint unsigned     default 0 not null,
  `content` text,
    excluded        tinyint(1) unsigned default 0 null,
  `placeholder` text,
    primary key (fhp_id, activity_type_id)
  PRIMARY KEY (`category_id`,`business_field_id`,`person_id`)
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Content of extra form field';
    comment 'flight hour pricing flight type list';


--
create table fhp_profile
-- Table structure for table `business_field_group`
(
--
    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 `business_field_group` (
create table file
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `label` varchar(255) DEFAULT NULL,
    id           int unsigned auto_increment
  `order_num` int(11) DEFAULT NULL,
        primary key,
  PRIMARY KEY (`id`)
    name          varchar(255)       null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    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
-- Table structure for table `criteria`
(
--
    id    int unsigned auto_increment
        primary key,
    label varchar(255) null
);


CREATE TABLE `criteria` (
create table flight
  `id` int(11) NOT NULL AUTO_INCREMENT,
(
   `label` varchar(255) DEFAULT NULL,
    id                   int unsigned auto_increment
  `predicate` text,
        primary key,
  `order_num` int(11) DEFAULT NULL,
    aircraft_id          int unsigned              null,
  `dsn` varchar(20) NOT NULL DEFAULT 'customer',
    start_date            datetime                  null,
  PRIMARY KEY (`id`)
    duration              int                       null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    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 `criteria_profile`
(
--
    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 `criteria_profile` (
create index idx_account_entry_id
  `criteria_id` int(10) unsigned NOT NULL,
    on flight_account_entry (account_entry_id);
  `profile_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`criteria_id`,`profile_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
create table flight_hours_pricing
-- Table structure for table `customer_bill_entry`
(
--
    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_entry` (
create table flight_pilot
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `flow_id` int(10) unsigned DEFAULT NULL,
    flight_id int unsigned           not null,
  `account_entry_id` int(10) unsigned DEFAULT NULL,
    pilot_id  int unsigned           not null,
  `account_entry_flow_id` int(10) unsigned DEFAULT NULL,
    status_id int unsigned           null,
  `owner_category` int(10) unsigned DEFAULT NULL,
    num      int unsigned default 0 not null,
  `owner_id` int(10) unsigned DEFAULT NULL,
    primary key (flight_id, pilot_id, num)
  `customer_bill_id` int(10) unsigned DEFAULT NULL,
)
  `product_id` int(10) unsigned DEFAULT NULL,
    comment 'list of crew for each flight';
  `qty` decimal(15,2) DEFAULT '0.00',
  `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_tank_qty
-- Table structure for table `customer_bill_file`
(
--
    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 `customer_bill_file` (
create table flight_track
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `file_type` varchar(255) DEFAULT NULL,
    id       int unsigned auto_increment
  `content` longblob,
        primary key,
  PRIMARY KEY (`id`)
    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 `customer_receipt_entry`
(
--
    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 `customer_receipt_entry` (
create table import
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `flow_id` int(10) unsigned DEFAULT NULL,
    id                 int unsigned auto_increment
  `account_entry_id` int(10) unsigned DEFAULT NULL,
        primary key,
  `account_entry_flow_id` int(10) unsigned DEFAULT NULL,
    label              varchar(255)                         not null,
  `owner_category` int(10) unsigned DEFAULT NULL,
    order_num          int                                   null,
  `owner_id` int(10) unsigned DEFAULT NULL,
    import_file_type    varchar(255) default 'csv-comma-CRLF' not null,
  `payment_type_id` int(10) unsigned DEFAULT NULL,
    match_query        text                                  null,
  `customer_receipt_id` int(10) unsigned DEFAULT NULL,
    uptodate_test_query text                                  null,
  `debit` decimal(15,5) DEFAULT '0.00000',
    update_query        text                                  null,
   `credit` decimal(15,5) DEFAULT '0.00000',
    update_activated    tinyint(1default 0               not null,
  `receipt_date` datetime DEFAULT NULL,
    login              varchar(255)                         null,
  `owner_email` varchar(255) DEFAULT NULL,
    hash_password      varchar(255)                         null,
  PRIMARY KEY (`id`),
    sync_task_name      varchar(255)                         null
  KEY `idx_account_entry_id` (`account_entry_id`),
)
  KEY `idx_account_entry_flow_id` (`account_entry_flow_id`)
    comment 'List of import';
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
create table ip_stopped
-- Table structure for table `customer_receipt_file`
(
--
    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 `customer_receipt_file` (
create table journal
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `file_type` varchar(255) DEFAULT NULL,
    id       int unsigned auto_increment
   `content` longblob,
        primary key,
  PRIMARY KEY (`id`)
    login    varchar(255)           null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    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
-- Table structure for table `default_display`
    on journal (action);
--


CREATE TABLE `default_display` (
create index idx_date_log
  `person_id` int(10) unsigned NOT NULL DEFAULT '0',
    on journal (date_log);
  `display_key` varchar(255) NOT NULL DEFAULT '',
  `display_value` text,
  PRIMARY KEY (`person_id`,`display_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


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


CREATE TABLE `default_multi_display` (
create table key_assignment
  `resource_type` char(30) NOT NULL,
(
  `person_id` int(10) unsigned NOT NULL DEFAULT '0',
    key_id      tinyint(2) unsigned default 0 not null
   `resource_id` varchar(100) NOT NULL,
        primary key,
  `display_value` text,
    key_name    tinytext                      null,
  PRIMARY KEY (`resource_type`,`person_id`,`resource_id`)
    aircraft_id int unsigned       default 0 null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    key_state   tinyint(1) unsigned default 0 null,
    key_word    bigint unsigned    default 0 null
);


--
create table key_host
-- Table structure for table `email_sent`
(
--
    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 `email_sent` (
create table key_log
  `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,
    log_date  datetime    null,
  `category` tinyint(3) unsigned NOT NULL,
    action    varchar(255) null,
  `category_id` int(10) unsigned NOT NULL,
    message   varchar(255) null,
  PRIMARY KEY (`id`),
    key_id    int unsigned null,
  UNIQUE KEY `message_id_UNIQUE` (`message_id`)
    person_id int unsigned null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    xmlrpc    int unsigned not null,
    error    varchar(255) null
)
    comment 'Key logs';


--
create table location
-- Table structure for table `email_sent_recipient`
(
--
    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 `email_sent_recipient` (
create table log
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
(
  `email_sent_id` bigint(20) unsigned NOT NULL,
    journal_id  int unsigned not null,
  `person_id` int(10) unsigned NOT NULL,
    action      varchar(255) null,
  `email` varchar(255) DEFAULT NULL,
    table_name  varchar(255) null,
  `recipient_type` tinyint(3) unsigned DEFAULT NULL,
    field_name  varchar(255) null,
  `smtp_status_category` varchar(30) DEFAULT NULL,
    field_value varchar(255) null
  `email_status` tinyint(3) unsigned DEFAULT NULL,
)
  `update_date` datetime NOT NULL,
    comment 'part of logs';
  PRIMARY KEY (`id`),
  KEY `email_sent_id_idx` (`email_sent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
create index idx_field_value
-- Table structure for table `exceptionnal_inst_date`
    on log (field_value);
--


CREATE TABLE `exceptionnal_inst_date` (
create index idx_journal_id
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    on log (journal_id);
  `person_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;


--
create index journal_id
-- Table structure for table `extra_field_profile`
    on log (journal_id);
--


CREATE TABLE `extra_field_profile` (
create table logger
  `business_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 (`business_field_id`,`booking_popup_display_4_profile_id`)
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    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 `facebook`
(
--
    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 `facebook` (
create table m_component
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `category` int(10) unsigned NOT NULL,
    id                 int unsigned auto_increment
  `owner_id` int(10) unsigned NOT NULL,
        primary key,
  `small` blob,
    parent_id          int unsigned       default 0 not null,
  `original` mediumblob NOT NULL,
    m_component_type_id int unsigned                 null,
  `label` text,
    resource_id        int unsigned                  null,
  `description` text,
    order_num          int                          not null,
  PRIMARY KEY (`id`)
    serial_number      varchar(255)                  null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    brandnew_date      datetime                      null,
    activated          tinyint(1) unsigned default 1 not null
);


--
create table m_component_type
-- Table structure for table `favorite_icao`
(
--
    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 `favorite_icao` (
create table m_component_type_parentality
  `icao` varchar(6) NOT NULL DEFAULT '',
(
  PRIMARY KEY (`icao`)
    m_component_type_id        int unsigned not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='favorite airfield list';
    m_component_type_parent_id int unsigned not null,
    primary key (m_component_type_id, m_component_type_parent_id)
);


--
create table m_history
-- Table structure for table `favorite_report`
(
--
    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 `favorite_report` (
create table m_operation
  `report_id` int(10) unsigned NOT NULL,
(
  PRIMARY KEY (`report_id`)
    id                  int unsigned auto_increment
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
        primary key,
    content            text        null,
    m_component_type_id int unsigned not null,
    order_num          int          not null
);


--
create table maintenance_history
-- Table structure for table `favorite_report_business_field`
(
--
    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 `favorite_report_business_field` (
create table maintenance_program
  `report_id` int(10) unsigned NOT NULL,
(
  `business_field_id` int(10) unsigned NOT NULL,
    id                                    int unsigned auto_increment
  `default_value` text,
        primary key,
  PRIMARY KEY (`report_id`,`business_field_id`)
    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 `favorite_report_profile`
(
--
    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 `favorite_report_profile` (
create table map_logger_monitoring
  `report_id` int(10) unsigned NOT NULL,
(
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
    record_id  int unsigned not null
  PRIMARY KEY (`report_id`,`profile_id`)
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    count_data int unsigned not null
)
    comment 'link between flight and track';


--
create table map_track
-- Table structure for table `fhp_aircraft_type`
(
--
    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 `fhp_aircraft_type` (
create table nationality
  `fhp_id` int(10) NOT NULL DEFAULT '0',
(
  `aircraft_type_id` int(10) NOT NULL DEFAULT '0',
    code  char(2) default '' not null
  PRIMARY KEY (`fhp_id`,`aircraft_type_id`)
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='flight hour pricing aircraft type list';
    label varchar(255)       not null
);


--
create table parameter
-- Table structure for table `fhp_flight_type`
(
--
    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 `fhp_flight_type` (
create table payment_distribution
  `fhp_id` int(10) NOT NULL DEFAULT '0',
(
  `activity_type_id` bigint(20) unsigned NOT NULL DEFAULT '0',
    payment_id        int unsigned        default 0 not null
  `excluded` tinyint(1) unsigned DEFAULT '0',
        primary key,
   PRIMARY KEY (`fhp_id`,`activity_type_id`)
    account_club_id    int unsigned                 null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='flight hour pricing flight type list';
    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 `fhp_profile`
(
--
    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 `fhp_profile` (
create table payment_type
  `fhp_id` int(10) unsigned NOT NULL,
(
  `profile_id` bigint(20) unsigned NOT NULL,
    id              int unsigned auto_increment
  `place_num` tinyint(1) unsigned NOT NULL DEFAULT '0',
        primary key,
  PRIMARY KEY (`fhp_id`,`profile_id`,`place_num`)
    name            text                          null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    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
-- Table structure for table `file`
(
--
    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 `file` (
create table person_awaiting_activation
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `name` varchar(255) DEFAULT NULL,
    id             int unsigned auto_increment
  `content` longblob,
        primary key,
  `file_type` varchar(255) DEFAULT NULL,
    login          varchar(255)       not null,
  `category` tinyint(1) unsigned DEFAULT NULL,
    ip              varchar(255)        not null,
  `owner_id` int(10) DEFAULT NULL,
    code            varchar(255)       not null,
  `file_group_id` int(10) DEFAULT NULL,
    category       varchar(255)       not null,
  PRIMARY KEY (`id`)
    expiration_date datetime            null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    used            tinyint(1) unsigned null
)
    comment 'member_awaiting_activation';


--
create table product
-- Table structure for table `file_group`
(
--
    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 `file_group` (
create table profile
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `label` varchar(255) DEFAULT NULL,
    id               bigint unsigned default 0 not null
  PRIMARY KEY (`id`)
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    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
-- Table structure for table `flight`
(
--
    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` (
create table profile_extra_field_join
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `aircraft_id` int(10) unsigned DEFAULT NULL,
    profile_id        bigint unsigned default 0 not null,
  `start_date` datetime DEFAULT NULL,
    business_field_id int unsigned   default 0 not null,
  `duration` int(11) DEFAULT NULL,
    primary key (profile_id, business_field_id)
  `activity_type_id` bigint(20) 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) DEFAULT NULL,
  `airborne` tinyint(1) unsigned DEFAULT NULL,
  `validated` tinyint(3) unsigned DEFAULT '0',
  `departure_icao_id` varchar(6) DEFAULT NULL,
  `arrival_icao_id` varchar(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='list of flight';


--
create table profile_profile_view
-- Table structure for table `flight_account_entry`
(
--
    profile_id          bigint unsigned not null,
    viewable_profile_id bigint unsigned not null,
    primary key (profile_id, viewable_profile_id)
);


CREATE TABLE `flight_account_entry` (
create table profile_required_account_type
  `flight_id` int(10) unsigned NOT NULL DEFAULT '0',
(
  `account_entry_id` int(10) unsigned NOT NULL DEFAULT '0',
    profile_id      bigint unsigned default 0 not null,
  PRIMARY KEY (`flight_id`,`account_entry_id`),
    account_type_id int unsigned   default 0 not null,
  KEY `idx_account_entry_id` (`account_entry_id`)
    primary key (profile_id, account_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='flight account entry join';
);


--
create table profile_resource_type_place
-- Table structure for table `flight_hours_pricing`
(
--
    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 `flight_hours_pricing` (
create table profile_resource_type_view
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `name` text,
    profile_id                bigint unsigned not null,
  `price_formula` text,
    viewable_resource_type_id int unsigned   not null,
  `left_account_id` int(10) unsigned DEFAULT NULL,
    primary key (profile_id, viewable_resource_type_id)
  `right_account_id` int(10) unsigned DEFAULT NULL,
);
  `left_account_type` tinyint(1) unsigned DEFAULT '0',
  `right_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,
  `product_id` int(10) unsigned DEFAULT NULL,
  `sale_trigger_id` int(10) unsigned DEFAULT NULL,
  `query` text,
  `variable_formula` varchar(255) DEFAULT NULL,
  `business_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 profile_validity_type_join
-- Table structure for table `flight_pilot`
(
--
    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 `flight_pilot` (
create table profile_validity_type_notification
  `flight_id` int(10) unsigned NOT NULL,
(
  `pilot_id` int(10) unsigned NOT NULL,
    profile_id      bigint unsigned default 0 not null,
  `status_id` int(10) DEFAULT NULL,
    validity_type_id int unsigned   default 0 not null,
  `num` int(10) unsigned NOT NULL DEFAULT '0',
    primary key (profile_id, validity_type_id)
  PRIMARY KEY (`flight_id`,`pilot_id`,`num`)
);
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='list of crew for each flight';


--
create table psp_return
-- Table structure for table `flight_tank_qty`
(
--
    id                int unsigned auto_increment
        primary key,
    psp_transaction_id int unsigned null,
    bank_answer        text        null,
    bank_misc          text        null
);


CREATE TABLE `flight_tank_qty` (
create table psp_transaction
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `flight_id` int(10) unsigned NOT NULL,
    id               int unsigned auto_increment
  `tank_id` int(10) unsigned NOT NULL,
        primary key,
  `quantity` varchar(255) DEFAULT NULL,
    credit_account_id int unsigned           not null,
   `after_flight` tinyint(1) NOT NULL DEFAULT '0',
    debit_account_id  int unsigned           not null,
  `account_id` int(10) unsigned DEFAULT NULL,
    transaction_date  datetime              not null,
  `pay_type` tinyint(1) unsigned DEFAULT NULL,
    amount            float                  not null,
  PRIMARY KEY (`id`)
    description      varchar(255)           null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    payment_type_id   int unsigned           not null,
    state            int unsigned default 0 not null,
    token            text                  null
);


--
create table psp_transaction_account_entry
-- Table structure for table `flight_track`
(
--
    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 `flight_track` (
create table regular_presence_inst_date
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `flight_id` int(10) NOT NULL,
    id         int unsigned auto_increment
  `track` mediumblob NOT NULL,
        primary key,
   PRIMARY KEY (`id`)
    person_id  int unsigned    null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    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 `flight_type_mandatory_validity_type`
(
--
    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 `flight_type_mandatory_validity_type` (
create table resource_exceptional_availability
  `activity_type_id` bigint(20) unsigned NOT NULL DEFAULT '0',
(
  `validity_type_id` int(10) unsigned NOT NULL,
    id          int unsigned auto_increment
  PRIMARY KEY (`activity_type_id`,`validity_type_id`)
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='list of mandatory qualification for each flight type';
    resource_id int unsigned       null,
    start_date  datetime            null,
    end_date    datetime            null,
    presence    tinyint(1) unsigned null
);


--
create table resource_regular_availability
-- Table structure for table `import`
(
--
    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 `import` (
create table resource_type
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `label` varchar(255) NOT NULL,
    id                   int unsigned auto_increment
  `order_num` int(11) DEFAULT NULL,
        primary key,
  `import_file_type` varchar(255) NOT NULL DEFAULT 'csv-comma-CRLF',
    name                varchar(255)                   null,
  `match_query` text,
    category            int unsigned                  null,
  `uptodate_test_query` text,
    seats_available      int                 default -1 null,
  `update_query` text,
    comments            varchar(255)                   null,
  `update_activated` tinyint(1) NOT NULL DEFAULT '0',
    order_num            int                            null,
  `login` varchar(255) DEFAULT NULL,
    activated            tinyint(1) unsigned default 1  not null,
  `hash_password` varchar(255) DEFAULT NULL,
    max_booking_duration int(10)             default -1 null,
  `sync_task_name` varchar(255) DEFAULT NULL,
    pictogram            int unsigned                  null
  PRIMARY KEY (`id`)
)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    comment 'List of resource type';


--
create table resource_type_place_tag
-- Table structure for table `ip_stopped`
(
--
    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 `ip_stopped` (
create table sale_2_stock
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `ip` varchar(255) DEFAULT NULL,
    id                           int unsigned auto_increment
  `counter` tinyint(1) unsigned NOT NULL,
        primary key,
  `expire_date` datetime NOT NULL,
    product_id                  int unsigned   null,
  PRIMARY KEY (`id`)
    stock_id                    int unsigned    not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='BLACKLISTED IP';
    stock_variation_qty_per_sale float default 0 not null
);


--
create table sale_2_validity_type
-- Table structure for table `journal`
(
--
    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 `journal` (
create index idx_product
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    on sale_2_validity_type (product_id);
  `login` varchar(255) DEFAULT NULL,
  `date_log` datetime DEFAULT NULL,
  `rights` text,
  `rights2` text,
  `action` varchar(255) DEFAULT NULL,
  `person_id` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_date_log` (`date_log`),
  KEY `idx_action` (`action`(8))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='list of logs';


--
create table sale_pricing
-- Table structure for table `key_alert`
(
--
    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 `key_alert` (
create table sale_pricing_product
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `alert_date` datetime DEFAULT NULL,
    sale_pricing_id int unsigned default 0 not null,
  `status` int(10) unsigned NOT NULL DEFAULT '0',
    product_id      int unsigned default 0 not null,
  PRIMARY KEY (`id`)
    primary key (sale_pricing_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Key alerts';
);


--
create table sale_pricing_profile
-- Table structure for table `key_assignment`
(
--
    sale_pricing_id int unsigned              not null,
    profile_id      bigint unsigned default 0 not null,
    primary key (sale_pricing_id, profile_id)
);


CREATE TABLE `key_assignment` (
create table sale_trigger
  `key_id` tinyint(2) unsigned NOT NULL DEFAULT '0',
(
  `key_name` tinytext,
    id            int(10) auto_increment
  `aircraft_id` int(10) unsigned DEFAULT '0',
        primary key,
  `key_state` tinyint(1) unsigned DEFAULT '0',
    name          varchar(255)                  null,
  `key_word` bigint(20) unsigned DEFAULT NULL,
    event          varchar(255)                 null,
  PRIMARY KEY (`key_id`)
    locked        tinyint(1) unsigned default 0 not null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='key assignment description';
    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 `key_host`
(
--
    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 `key_host` (
create table statistic
  `id` tinyint(2) NOT NULL AUTO_INCREMENT,
(
  `timeout` tinyint(2) unsigned NOT NULL DEFAULT '10',
    name      varchar(255)               not null,
  `num_key` tinyint(2) unsigned NOT NULL DEFAULT '8',
    of_version tinyint(3)     default 0    not null,
  `ipkey` varchar(50) DEFAULT NULL,
    value      decimal(15, 2) default 0.00 not null,
  `httpport` int(11) DEFAULT '4080',
    primary key (name, of_version)
  PRIMARY KEY (`id`)
);
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='key host configuration';


--
create table status
-- Table structure for table `key_log`
(
--
    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 `key_log` (
create table 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,
    label        varchar(255) not null,
  `key_id` int(10) unsigned DEFAULT NULL,
    stock_type_id int unsigned not null
  `person_id` int(10) unsigned DEFAULT NULL,
);
  `xmlrpc` int(10) unsigned NOT NULL,
  `error` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Key logs';


--
create table stock_level
-- Table structure for table `location`
(
--
    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 `location` (
create table stock_type
  `icao_name` varchar(6) NOT NULL,
(
  `name` varchar(64) NOT NULL,
    id    int unsigned auto_increment
  `latitude` double DEFAULT NULL,
        primary key,
  `longitude` double DEFAULT NULL,
    label varchar(255) not null,
  `altitude` int(7) DEFAULT NULL,
    unit  varchar(255) not null
  `weather_station` int(1) unsigned DEFAULT NULL,
);
  `asked_counter` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`icao_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='airfields coord';


--
create table stock_variation
-- Table structure for table `log`
(
--
    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 `log` (
create table stock_variation_account_entry
  `journal_id` int(10) unsigned NOT NULL,
(
  `action` varchar(255) DEFAULT NULL,
    product_id            int unsigned default 0 not null,
  `table_name` varchar(255) DEFAULT NULL,
    stock_variation_id    int unsigned          not null,
  `field_name` varchar(255) DEFAULT NULL,
    account_entry_flow_id int unsigned          not null,
  `field_value` varchar(255) DEFAULT NULL,
    person_id            int unsigned          not null,
  KEY `idx_journal_id` (`journal_id`),
    primary key (product_id, stock_variation_id, account_entry_flow_id)
  KEY `idx_field_value` (`field_value`)
);
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='part of logs';


--
create index idx_account_entry_flow_id
-- Table structure for table `logger`
    on stock_variation_account_entry (account_entry_flow_id);
--


CREATE TABLE `logger` (
create table structure
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `serial_number` int(10) unsigned DEFAULT NULL,
    id                           int(10)         default 0 not null
  `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,
    info_cell                    text                      null,
  PRIMARY KEY (`id`)
    logo                          longblob                  null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Logger parameters';
    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
-- Table structure for table `login_stopped`
(
--
    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 `login_stopped` (
create table supplier_bill_account_entry
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `login` varchar(255) DEFAULT NULL,
    supplier_bill_id      int unsigned not null,
  `counter` tinyint(3) unsigned NOT NULL,
    account_entry_flow_id int unsigned not null,
  `expire_date` datetime NOT NULL,
    primary key (supplier_bill_id, account_entry_flow_id)
  PRIMARY KEY (`id`)
);
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='BLACKLISTED LOGIN';


--
create index idx_account_entry_flow_id
-- Table structure for table `m_component`
    on supplier_bill_account_entry (account_entry_flow_id);
--


CREATE TABLE `m_component` (
create table supplier_bill_email_parsed
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `parent_id` int(10) unsigned NOT NULL DEFAULT '0',
    id                   int(10) auto_increment
  `m_component_type_id` int(10) unsigned DEFAULT NULL,
        primary key,
  `resource_id` int(10) unsigned DEFAULT NULL,
    uid                  int(10)     null,
  `order_num` int(11) NOT NULL,
    email_address        varchar(255) null,
  `serial_number` varchar(255) DEFAULT NULL,
    supplier_bill_file_id int(10)     null
  `brandnew_date` datetime DEFAULT NULL,
);
  `activated` tinyint(1) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
create table supplier_bill_file
-- Table structure for table `m_component_type`
(
--
    id        int unsigned auto_increment
        primary key,
    file_type varchar(255) null,
    content  longblob    null,
    name      varchar(255) not null
);


CREATE TABLE `m_component_type` (
create table supplier_bill_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,
    supplier_account_id int unsigned not null,
  `is_maintenance_check` tinyint(1) unsigned NOT NULL DEFAULT '1',
    name                varchar(255) null,
  `order_num` int(11) NOT NULL,
    account_id          int unsigned not null,
  `label` varchar(255) DEFAULT NULL,
    vat_account_id      int unsigned null,
  `description` varchar(255) DEFAULT NULL,
    supplier_budget_id  int unsigned null,
  `manufacturer` varchar(255) DEFAULT NULL,
    account_budget_id   int unsigned null,
  `manufacturer_reference` varchar(255) DEFAULT NULL,
    vat_budget_id      int unsigned null,
  `part_number` varchar(255) DEFAULT NULL,
    rule                text        null,
  `periodicity` int(10) unsigned DEFAULT NULL,
    sender_email        varchar(255) null,
   `tolerance` int(10) unsigned NOT NULL DEFAULT '0',
    subject            varchar(255) null
  `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 table_trigger
-- Table structure for table `m_component_type_parentality`
(
--
    id              int unsigned auto_increment
        primary key,
    monitored_table varchar(255) not null,
    trigger_formula varchar(255) not null
);


CREATE TABLE `m_component_type_parentality` (
create table tank
   `m_component_type_id` int(10) unsigned NOT NULL,
(
  `m_component_type_parent_id` int(10) unsigned NOT NULL,
    id                int unsigned auto_increment
  PRIMARY KEY (`m_component_type_id`,`m_component_type_parent_id`)
        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 `m_history`
(
--
    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 `m_history` (
create table track_record_data
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `m_component_id` int(10) unsigned NOT NULL,
    id           int unsigned auto_increment
   `install_date` datetime DEFAULT NULL,
        primary key,
  `remove_date` datetime DEFAULT NULL,
    record_id    int unsigned       not null,
  `total_hours_on_install` int(10) unsigned NOT NULL DEFAULT '0',
    record_date   datetime           null,
  `threshold_hours` int(10) unsigned NOT NULL DEFAULT '0',
    longitude    double              null,
  `threshold_date` datetime DEFAULT NULL,
    latitude      double              null,
  `threshold_date_locked` tinyint(1) unsigned NOT NULL DEFAULT '0',
    altitude      int(7)              null,
  `threshold_hours_locked` tinyint(1) unsigned NOT NULL DEFAULT '0',
    speed        double              null,
  PRIMARY KEY (`id`)
    track        int(3) unsigned     null,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    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 `m_operation`
    on track_record_data (record_id);
--


CREATE TABLE `m_operation` (
create table track_resource
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `content` text,
    id  int unsigned auto_increment
   `m_component_type_id` int(10) unsigned NOT NULL,
        primary key,
  `order_num` int(11) NOT NULL,
    name varchar(255) not null
  PRIMARY KEY (`id`)
);
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
create table uncomp_flight_type
-- Table structure for table `maintenance_history`
(
--
    id1 int unsigned null,
    id2 int unsigned null
)
    comment 'List of sales';


CREATE TABLE `maintenance_history` (
create table validity_type
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `resource_id` int(10) unsigned DEFAULT NULL,
    id                       int unsigned auto_increment
  `maintenance_program_id` int(10) unsigned DEFAULT NULL,
        primary key,
  `frame_hours_at_work_start` int(10) DEFAULT NULL,
    name                    varchar(255)                   not null,
  `date_work_end` date DEFAULT NULL,
    time_limitation          tinyint(1)         default 0  not null,
  `min_overhaul_counter` int(10) DEFAULT NULL,
    ident_value_enable      tinyint(1)         default 0  not null,
  `max_overhaul_counter` int(10) DEFAULT NULL,
    grant_date_enable        tinyint(1)         default 0 not null,
  `min_overhaul_date` date DEFAULT NULL,
    mandatory                tinyint(1)         default 0 not null,
  `max_overhaul_date` date DEFAULT NULL,
    experience_formula      text                          null,
  `min_next_overhaul_counter` int(10) DEFAULT NULL,
    mandatory_access_control tinyint(1) unsigned default 0  null,
  `max_next_overhaul_counter` int(10) DEFAULT NULL,
    alert_on_login          int(10)             default -2 null,
  `min_next_overhaul_date` date DEFAULT NULL,
    first_reminder_alert    int unsigned        default 0  null,
  `max_next_overhaul_date` date DEFAULT NULL,
    reminder_frequency_alert int unsigned        default 0  null,
  `first_reference_visit` tinyint(1) unsigned NOT NULL DEFAULT '0',
    associate_attachment    tinyint(1) unsigned default 0  not null,
  `is_visit` tinyint(1) unsigned NOT NULL DEFAULT '0',
    certification_process    tinyint(1)         default 0  not null,
  `action_type` tinyint(1) unsigned DEFAULT NULL,
    is_contract              tinyint(1)         default 0  not null,
  `previous_line_linked_time_action` int(10) DEFAULT NULL,
    contract_filename        varchar(255)                   not null,
  `previous_line_linked_calendar_action` int(10) DEFAULT NULL,
    contract_file            mediumblob                    not null,
  `next_line_linked_time_action` int(10) DEFAULT NULL,
    contract_file_extension  varchar(4)                     not null,
  `next_line_linked_calendar_action` int(10) DEFAULT NULL,
    is_OF_contract          tinyint(1)         default 0  not null,
  `time_tolerance` int(10) DEFAULT NULL,
    activated                tinyint(1) unsigned default 1  not null
  `calendar_tolerance` int(10) DEFAULT NULL,
);
  `time_periodicity_component` int(10) DEFAULT NULL,
  `calendar_periodicity_component` int(10) DEFAULT NULL,
  `time_periodicity` int(10) DEFAULT NULL,
  `calendar_periodicity` int(10) DEFAULT NULL,
  `reference_overhaul_counter` int(10) DEFAULT NULL,
  `reference_overhaul_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
create table validity
-- Table structure for table `maintenance_program`
(
--
    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 `maintenance_program` (
create table validity_type_page
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
(
  `resource_id` int(10) unsigned DEFAULT NULL,
    id               int unsigned auto_increment
  `component_type_id` int(10) unsigned DEFAULT NULL,
        primary key,
  `description` varchar(255) DEFAULT NULL,
    validity_type_id int unsigned       not null,
  `time_periodicity` int(10) DEFAULT NULL,
    page_index      int unsigned       not null,
  `calendar_periodicity` int(10) DEFAULT NULL,
    label            varchar(40)         not null,
  `time_first_reference` int(10) DEFAULT NULL,
    is_mandatory    tinyint(1) unsigned not null,
  `calendar_first_reference` date DEFAULT NULL,
    constraint validity_type_page_validity_type_id_page_index_uindex
  `time_tolerance` int(10) DEFAULT NULL,
        unique (validity_type_id, page_index),
  `calendar_tolerance` int(10) DEFAULT NULL,
    constraint validity_type_page_validity_type_id_fk
  `frame_hours_at_work_start` int(10) DEFAULT NULL,
        foreign key (validity_type_id) references validity_type (id)
  `date_work_end` date DEFAULT NULL,
);
  `time_elapsed` int(10) DEFAULT NULL,
  `calendar_elapsed` int(10) DEFAULT NULL,
  `time_remaining` int(10) DEFAULT NULL,
  `calendar_remaining` int(10) DEFAULT NULL,
  `min_next_overhaul_counter` int(10) DEFAULT NULL,
  `max_next_overhaul_counter` int(10) DEFAULT NULL,
  `min_next_overhaul_date` date DEFAULT NULL,
  `max_next_overhaul_date` date DEFAULT NULL,
  `effective_hours_at_work_start` int(10) DEFAULT NULL,
  `theoretical_overhaul_counter` int(10) DEFAULT NULL,
  `intelligent_overhaul_counter` int(10) DEFAULT NULL,
  `intelligent_min_next_overhaul_counter` int(10) DEFAULT NULL,
  `intelligent_max_next_overhaul_counter` int(10) DEFAULT NULL,
  `effective_date_at_work_end` date DEFAULT NULL,
  `theoretical_overhaul_date` date DEFAULT NULL,
  `intelligent_overhaul_date` date DEFAULT NULL,
  `intelligent_min_next_overhaul_date` date DEFAULT NULL,
  `intelligent_max_next_overhaul_date` date DEFAULT NULL,
  `overlapping_group_id` int(10) unsigned DEFAULT NULL,
  `is_visit_rg` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
create table validity_page
-- Table structure for table `maintenance_view`
(
--
    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 `maintenance_view` (
create table validity_2_validity_page
  `id` int(10) unsigned NOT NULL DEFAULT '0',
(
  `frame_total_time` int(10) DEFAULT NULL,
    validity_id      int unsigned not null,
  `frame_time_since_rg` int(10) DEFAULT NULL,
    validity_page_id int unsigned not null,
  `engine_time_since_rg` int(10) DEFAULT NULL,
    primary key (validity_id, validity_page_id),
  `propeller_time_since_rg` int(10) DEFAULT NULL,
    constraint validity_2_validity_page_validity_id_fk
  `potential_remaining_next_time_action` int(10) DEFAULT NULL,
        foreign key (validity_id) references validity (id),
  `potential_remaining_next_time_visit` int(10) DEFAULT NULL,
    constraint validity_2_validity_page_validity_page_id_fk
  `potential_remaining_next_priority_time_action` int(10) DEFAULT NULL,
        foreign key (validity_page_id) references validity_page (id)
  `potential_remaining_next_calendar_action` int(10) DEFAULT NULL,
);
  `potential_remaining_next_calendar_visit` int(10) DEFAULT NULL,
  `potential_remaining_next_priority_calendar_action` int(10) DEFAULT NULL,
  `next_time_action_id` int(10) unsigned DEFAULT NULL,
  `next_time_visit_id` int(10) unsigned DEFAULT NULL,
  `next_priority_time_action_id` int(10) unsigned DEFAULT NULL,
  `next_calendar_action_id` int(10) unsigned DEFAULT NULL,
  `next_calendar_visit_id` int(10) unsigned DEFAULT NULL,
  `next_priority_calendar_action_id` int(10) unsigned DEFAULT NULL,
  `overhaul_counter_action` int(10) DEFAULT NULL,
  `overhaul_counter_visit` int(10) DEFAULT NULL,
  `overhaul_date_action` date DEFAULT NULL,
  `overhaul_date_visit` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
create table variable
-- Table structure for table `map_logger_monitoring`
(
--
    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 `map_logger_monitoring` (
create table variable_value
  `record_id` int(10) unsigned NOT NULL,
(
  `count_data` int(10) unsigned NOT NULL,
    id          int auto_increment
   PRIMARY KEY (`record_id`)
        primary key,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='link between flight and track';
    variable_id  int           not null,
    assign_value decimal(15, 2) not null,
    start_date   datetime      not null
);


--
create table web_feed
-- Table structure for table `map_track`
(
--
    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 `map_track` (
create table web_feed_channel
  `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,
    label varchar(255) not null
  `start_time` datetime DEFAULT NULL,
);
  `latitude_max` double DEFAULT NULL,
  `longitude_max` double DEFAULT NULL,
  `latitude_min` double DEFAULT NULL,
  `longitude_min` double DEFAULT NULL,
  `visible` tinyint(1) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Tracks split from GDR';


--
create definer = overallCustomer@localhost view eligible_pilot_for_first_flight as
-- Table structure for table `nationality`
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 `nationality` (
create definer = overallCustomer@localhost view eligible_resource_for_first_flight as
  `code` char(2) NOT NULL DEFAULT '',
select `of40_aeroclublys`.`resource`.`id` AS `resource_id`
  `label` varchar(255) DEFAULT NULL,
from ((`of40_aeroclublys`.`resource` join `of40_aeroclublys`.`business_field_content` on ((
  PRIMARY KEY (`code`)
        `of40_aeroclublys`.`business_field_content`.`category_id` = `of40_aeroclublys`.`resource`.`id`)))
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
        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
-- Table structure for table `parameter`
    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 `parameter` (
    SET rlongitude1 = RADIANS(longitude1);
  `code` varchar(255) NOT NULL DEFAULT '',
    SET rlatitude1 = RADIANS(latitude1);
  `key_id` int(10) unsigned NOT NULL DEFAULT '0',
    SET rlongitude2 = RADIANS(longitude2);
  `enabled` tinyint(1) unsigned DEFAULT '0',
    SET rlatitude2 = RADIANS(latitude2);
  `int_value` int(10) unsigned DEFAULT '0',
    SET dlongitude = (rlongitude2 - rlongitude1) / 2;
  `char_value` text,
    SET dlatitude = (rlatitude2 - rlatitude1) / 2;
  PRIMARY KEY (`code`,`key_id`)
    SET a = SIN(dlatitude) * SIN(dlatitude) + COS(rlatitude1) * COS(rlatitude2) * SIN(dlongitude) * SIN(dlongitude);
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='APPLICATION PARAMETERS';
    RETURN (6378137 * 2 * ATAN2(SQRT(a), SQRT(1 - a)));
END;


--
create
-- Table structure for table `payment_distribution`
    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 `payment_distribution` (
    SELECT IF(replacedString <> '0.00' AND replacedString <> '0', TRIM(TRAILING '0' FROM replacedString),
  `payment_id` int(10) unsigned NOT NULL DEFAULT '0',
              replacedString)
  `account_club_id` int(10) unsigned DEFAULT NULL,
    INTO replacedString;
  `person_delivery` tinyint(1) unsigned DEFAULT '0',
    SELECT TRIM(TRAILING '.' FROM replacedString) INTO replacedString;
  `member_budget_id` int(10) unsigned DEFAULT NULL,
    SELECT REPLACE(replacedString, '.', IFNULL(@decimalSeparator, '.')) INTO replacedString;
  `treasury_budget_id` int(10) unsigned DEFAULT NULL,
    RETURN replacedString;
  PRIMARY KEY (`payment_id`)
END;
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='payment distribution description';


--
create
-- Table structure for table `payment_summary_file`
    definer = root@localhost function isBalancedAccountEntryFlow(flowId int) returns int
--
BEGIN
    DECLARE isBalanced INT;


CREATE TABLE `payment_summary_file` (
    SELECT IF(SUM(debit) <> SUM(credit), 0, 1)
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    into isBalanced
  `file_type` varchar(255) DEFAULT NULL,
    FROM account_entry
  `content` longblob,
    WHERE flow_id = flowId
  `record_date` datetime DEFAULT NULL,
    GROUP BY flow_id;
  `total_entry` int(10) unsigned DEFAULT NULL,
  `total_amount` decimal(15,5) unsigned DEFAULT NULL,
  `payment_type_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
    RETURN isBalanced;
-- Table structure for table `payment_type`
END;
--


CREATE TABLE `payment_type` (
create
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    definer = root@localhost function nearestPoint(latRef double, longRef double, distanceMax int) returns varchar(6)
  `name` text,
BEGIN
  `text_field_label` text,
    DECLARE rlongitude1 DOUBLE;
  `only_admin` tinyint(1) unsigned NOT NULL DEFAULT '0',
    DECLARE rlatitude1 DOUBLE;
  `order_num` int(11) DEFAULT NULL,
    DECLARE rlongitude2 DOUBLE;
  `pos_key_id` int(10) unsigned DEFAULT NULL,
    DECLARE rlatitude2 DOUBLE;
  PRIMARY KEY (`id`)
    DECLARE a DOUBLE;
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='type of payment description';
    DECLARE icao_name VARCHAR(6);
    DECLARE lenght DOUBLE;


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


CREATE TABLE `person` (
    SELECT loc1.icao_name,
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
          distanceBetween2Point(
  `name` varchar(255) DEFAULT NULL,
                  (SELECT loc2.latitude FROM location AS loc2 WHERE loc2.icao_name = loc1.icao_name),
  `hash_password` varchar(255) NOT NULL,
                  (SELECT loc3.longitude FROM location AS loc3 WHERE loc3.icao_name = loc1.icao_name),
  `first_name` varchar(255) DEFAULT NULL,
                  latRef,
  `last_name` varchar(255) DEFAULT NULL,
                  longRef) AS distance
  `profile` bigint(20) unsigned DEFAULT NULL,
    FROM location AS loc1
  `view_type` int(10) unsigned DEFAULT NULL,
    WHERE IF(rlongitude2 < rlongitude1,
  `view_width` tinyint(3) unsigned NOT NULL DEFAULT '12',
            loc1.longitude BETWEEN rlongitude2 AND rlongitude1,
  `view_height` tinyint(4) unsigned NOT NULL DEFAULT '30',
            loc1.longitude BETWEEN rlongitude1 AND rlongitude2)
  `email` varchar(255) DEFAULT NULL,
      AND IF(rlatitude2 < rlatitude1,
  `timezone` varchar(255) DEFAULT NULL,
            loc1.latitude BETWEEN rlatitude2 AND rlatitude1,
  `address` varchar(255) DEFAULT NULL,
            loc1.latitude BETWEEN rlatitude1 AND rlatitude2)
  `zipcode` varchar(255) DEFAULT NULL,
    HAVING distance < distanceMax
  `city` varchar(255) DEFAULT NULL,
    ORDER BY distance
  `state` varchar(255) DEFAULT NULL,
    LIMIT 1
  `country` varchar(255) DEFAULT NULL,
    INTO icao_name, lenght;
  `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,
  `guid` varchar(255) DEFAULT NULL,
  `activity_notification` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='used for authentication';


--
    RETURN icao_name;
-- Table structure for table `person_awaiting_activation`
END;
--


CREATE TABLE `person_awaiting_activation` (
create
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    definer = overallCustomer@localhost function sexa2HoursHundredths(sexacentimal int) returns varchar(255)
  `login` varchar(255) DEFAULT NULL,
BEGIN
  `ip` varchar(255) DEFAULT NULL,
    DECLARE convertedValue VARCHAR(255);
  `code` varchar(255) DEFAULT NULL,
    DECLARE hours VARCHAR(255);
  `category` varchar(255) DEFAULT NULL,
    DECLARE roundedHours VARCHAR(255);
  `expiration_date` datetime DEFAULT NULL,
    DECLARE remainingTime VARCHAR(255);
  `used` tinyint(1) unsigned DEFAULT NULL,
    DECLARE decimalSeparator VARCHAR(1);
  PRIMARY KEY (`id`)
    SET hours = sexacentimal / 600;
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    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 `product`
                  roundedHours, decimalSeparator, LPAD(FLOOR(remainingTime / 6), 2, '0')
--
              )
    INTO convertedValue;


CREATE TABLE `product` (
    RETURN convertedValue;
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
END;
  `label` varchar(255) DEFAULT NULL,
  `unit` varchar(255) DEFAULT NULL,
  `free_sale` tinyint(1) NOT NULL DEFAULT '0',
  `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
-- Table structure for table `profile`
    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` (
    SELECT CONCAT(
  `id` bigint(20) unsigned NOT NULL DEFAULT '0',
                  roundedHours, ':', LPAD(FLOOR(remainingTime / 10), 2, '0')
  `name` varchar(255) DEFAULT NULL,
              )
  `permits` int(10) unsigned DEFAULT NULL,
    INTO convertedValue;
  `permits2` int(10) unsigned NOT NULL DEFAULT '0',
  `permits3` int(10) unsigned NOT NULL DEFAULT '0',
  `pictogram` int(10) unsigned DEFAULT NULL,
  `default_status_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
    RETURN convertedValue;
-- Table structure for table `profile_accounting_notification`
END;
--


CREATE TABLE `profile_accounting_notification` (
create
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
    definer = root@localhost function stripChars(word varchar(255)) returns varchar(255)
  `accounting_id` int(10) unsigned NOT NULL DEFAULT '0',
BEGIN
  PRIMARY KEY (`profile_id`,`accounting_id`)
    DECLARE stripWord VARCHAR(255);
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    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
-- Table structure for table `profile_extra_field_join`
    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 `profile_extra_field_join` (
  `profile_id` bigint(20) unsigned NOT NULL,
  `business_field_id` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`profile_id`,`business_field_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
    SELECT balance_date, IFNULL(debit, 0.00), IFNULL(credit, 0.00)
-- Table structure for table `profile_profile_view`
    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 `profile_profile_view` (
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `viewable_profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`profile_id`,`viewable_profile_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
    IF (SELECT FOUND_ROWS()) = 0 THEN
-- Table structure for table `profile_required_account_type`
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit, balanceCredit;
--
    END IF;


CREATE TABLE `profile_required_account_type` (
    SELECT IFNULL(SUM(debit), 0.00) + balanceDebit, IFNULL(SUM(credit), 0.00) + balanceCredit
  `profile_id` bigint(20) unsigned NOT NULL,
    INTO totalDebit, totalCredit
  `account_type_id` int(10) unsigned NOT NULL,
    FROM account_entry
  PRIMARY KEY (`profile_id`,`account_type_id`)
    WHERE account_id = accountId
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      AND account_date >= CAST(balanceDate AS DATETIME)
      AND account_date < CAST(endDate AS DATETIME);


--
    SELECT (totalCredit - totalDebit) INTO totalBalance;
-- Table structure for table `profile_resource_type_place`
--


CREATE TABLE `profile_resource_type_place` (
    RETURN totalBalance;
  `profile_id` bigint(20) unsigned NOT NULL,
END;
  `resource_type_id` int(10) unsigned NOT NULL,
  `place_num` int(10) unsigned NOT NULL,
  PRIMARY KEY (`profile_id`,`resource_type_id`,`place_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
create
-- Table structure for table `profile_resource_type_view`
    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 `profile_resource_type_view` (
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `viewable_resource_type_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`profile_id`,`viewable_resource_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
    SELECT balance_date, IFNULL(credit, 0.00)
-- Table structure for table `profile_validity_type_join`
    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 `profile_validity_type_join` (
  `profile_id` bigint(20) unsigned NOT NULL,
  `validity_type_id` int(10) unsigned NOT NULL,
  `manage4oneself` int(1) NOT NULL DEFAULT '0',
  `certify` int(1) NOT NULL DEFAULT '0',
  `optional_contract` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`profile_id`,`validity_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
    IF (SELECT FOUND_ROWS()) = 0 THEN
-- Table structure for table `profile_validity_type_notification`
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceCredit;
--
    END IF;


CREATE TABLE `profile_validity_type_notification` (
    SELECT IFNULL(SUM(credit), 0.00) + balanceCredit
  `profile_id` bigint(20) unsigned NOT NULL DEFAULT '0',
    INTO totalCredit
  `validity_type_id` int(10) unsigned NOT NULL DEFAULT '0',
    FROM account_entry
  PRIMARY KEY (`profile_id`,`validity_type_id`)
    WHERE account_id = accountId
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      AND account_date >= CAST(balanceDate AS DATETIME)
      AND account_date < CAST(endDate AS DATETIME);


--
    RETURN totalCredit;
-- Table structure for table `psp_return`
END;
--


CREATE TABLE `psp_return` (
create
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    definer = overallCustomer@localhost function sumAccountEntryDebit(accountId int, endDate datetime) returns decimal(15, 2)
  `psp_transaction_id` int(10) unsigned DEFAULT NULL,
BEGIN
  `bank_answer` text,
    DECLARE balanceDate DATETIME;
  `bank_misc` text,
    DECLARE balanceDebit DECIMAL(15, 2);
  PRIMARY KEY (`id`)
    DECLARE totalDebit DECIMAL(15, 2);
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `psp_transaction`
--


CREATE TABLE `psp_transaction` (
    SELECT balance_date, IFNULL(debit, 0.00)
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    INTO balanceDate, balanceDebit
  `credit_account_id` int(10) unsigned NOT NULL,
    FROM balance_date
  `debit_account_id` int(10) unsigned NOT NULL,
            LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId)
  `transaction_date` datetime NOT NULL,
    WHERE balance_date.balance_date < endDate
  `amount` float NOT NULL,
    ORDER BY balance_date DESC
  `description` varchar(255) DEFAULT NULL,
    LIMIT 1;
  `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;


--
-- Table structure for table `psp_transaction_account_entry`
--


CREATE TABLE `psp_transaction_account_entry` (
    IF (SELECT FOUND_ROWS()) = 0 THEN
  `psp_transaction_id` int(10) unsigned NOT NULL DEFAULT '0',
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit;
  `account_entry_flow_id` int(10) unsigned NOT NULL DEFAULT '0',
    END IF;
  PRIMARY KEY (`psp_transaction_id`,`account_entry_flow_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
    SELECT IFNULL(SUM(debit), 0.00) + balanceDebit
-- Table structure for table `regular_presence_inst_date`
    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 `regular_presence_inst_date` (
    RETURN totalDebit;
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
END;
  `person_id` int(10) unsigned DEFAULT NULL,
  `start_day` tinyint(3) unsigned DEFAULT NULL,
  `end_day` tinyint(3) unsigned DEFAULT NULL,
  `start_hour` time DEFAULT NULL,
  `end_hour` time DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
create
-- Table structure for table `resource`
    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 `resource` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `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',
  `color` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
    SELECT balance_date, IFNULL(debit, 0.00), IFNULL(credit, 0.00)
-- Table structure for table `resource_exceptional_availability`
    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 `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;


--
    IF (SELECT FOUND_ROWS()) = 0 THEN
-- Table structure for table `resource_regular_availability`
        SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit, balanceCredit;
--
    END IF;


CREATE TABLE `resource_regular_availability` (
    SELECT IFNULL(SUM(debit), 0.00) + balanceDebit, IFNULL(SUM(credit), 0.00) + balanceCredit
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    INTO totalDebit, totalCredit
  `resource_id` int(10) unsigned DEFAULT NULL,
    FROM account_entry
  `start_day` tinyint(1) unsigned DEFAULT NULL,
    WHERE account_id = accountId
  `end_day` tinyint(1) 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,
      AND validated = 1;
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
    SELECT (totalCredit - totalDebit) INTO totalBalance;
-- Table structure for table `resource_type`
--


CREATE TABLE `resource_type` (
    RETURN totalBalance;
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
END;</sql>
  `name` varchar(255) DEFAULT NULL,
  `category` int(10) unsigned NOT NULL,
  `seats_available` int(11) DEFAULT '-1',
  `comments` varchar(255) DEFAULT NULL,
  `order_num` int(11) DEFAULT NULL,
  `activated` tinyint(1) 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;
 
--
-- Table structure for table `resource_type_place_tag`
--
 
CREATE TABLE `resource_type_place_tag` (
  `resource_type_id` int(10) unsigned NOT NULL,
  `place_num` tinyint(1) unsigned NOT NULL,
  `place_tag` varchar(255) DEFAULT NULL,
  `place_quantity` int(10) DEFAULT '-1',
  PRIMARY KEY (`resource_type_id`,`place_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Table structure for table `sale_2_stock`
--
 
CREATE TABLE `sale_2_stock` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(10) unsigned DEFAULT NULL,
  `stock_id` int(10) unsigned NOT NULL,
  `stock_variation_qty_per_sale` float NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Table structure for table `sale_2_validity_type`
--
 
CREATE TABLE `sale_2_validity_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `validity_type_id` int(10) unsigned NOT NULL,
  `new_formula` varchar(255) DEFAULT NULL,
  `update_formula` varchar(255) DEFAULT NULL,
  `product_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `validity_type_id` (`validity_type_id`),
  KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Table structure for table `sale_pricing`
--
 
CREATE TABLE `sale_pricing` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(255) DEFAULT NULL,
  `price_formula` text,
  `debit_account_id` int(10) unsigned NOT NULL,
  `credit_account_id` int(10) unsigned NOT NULL,
  `debit_account_type` int(10) unsigned NOT NULL,
  `credit_account_type` int(10) unsigned NOT NULL,
  `debit_budget_id` int(10) unsigned DEFAULT NULL,
  `credit_budget_id` int(10) unsigned DEFAULT NULL,
  `order_num` int(10) 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;
 
--
-- Table structure for table `sale_pricing_product`
--
 
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;
 
--
-- Table structure for table `sale_pricing_profile`
--
 
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;
 
--
-- Table structure for table `sale_trigger`
--
 
CREATE TABLE `sale_trigger` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `event` varchar(255) DEFAULT NULL,
  `locked` tinyint(1) unsigned DEFAULT '0',
  `query` text,
  `validate_entry` tinyint(1) DEFAULT '0',
  `group_sales` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Table structure for table `spreadsheet_parameter`
--
 
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) 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;
 
--
-- Table structure for table `statistic`
--
 
CREATE TABLE `statistic` (
  `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;
 
--
-- Table structure for table `status`
--
 
CREATE TABLE `status` (
  `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';
 
--
-- Table structure for table `stock`
--
 
CREATE TABLE `stock` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(255) DEFAULT NULL,
  `stock_type_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Table structure for table `stock_level`
--
 
CREATE TABLE `stock_level` (
  `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;
 
--
-- Table structure for table `stock_type`
--
 
CREATE TABLE `stock_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(255) DEFAULT NULL,
  `unit` varchar(255) DEFAULT 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 DEFAULT 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 `structure`
--
 
CREATE TABLE `structure` (
  `id` int(10) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `info_cell` text,
  `logo` longblob,
  `logo_name` varchar(255) DEFAULT NULL,
  `logo_ext` varchar(25) DEFAULT NULL,
  `logo_size` int(11) DEFAULT NULL,
  `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,
  `structure_site_url` varchar(255) DEFAULT NULL,
  `default_timezone` varchar(255) DEFAULT NULL,
  `lang` varchar(255) DEFAULT 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,
  `default_activity_notification` bigint(20) unsigned DEFAULT NULL,
  `siren` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='DESCRIPTION OF ALL AIRCLUBS';
 
--
-- 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` datetime DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `ordinal` int(10) unsigned NOT NULL,
  `validated` tinyint(1) DEFAULT '0',
  `supplier_bill_file_id` int(10) DEFAULT NULL,
  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_email_parsed`
--
 
CREATE TABLE `supplier_bill_email_parsed` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `uid` int(10) DEFAULT NULL,
  `email_address` varchar(255) DEFAULT NULL,
  `supplier_bill_file_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Table structure for table `supplier_bill_file`
--
 
CREATE TABLE `supplier_bill_file` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `file_type` varchar(255) DEFAULT NULL,
  `content` longblob,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`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,
  `rule` text,
  `sender_email` varchar(255) DEFAULT NULL,
  `subject` varchar(255) 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) DEFAULT NULL,
  `max_quantity` decimal(15,2) 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` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `person_id` int(10) unsigned NOT NULL,
  `validity_type_id` int(10) unsigned NOT NULL,
  `registration_date` datetime NOT NULL,
  `expire_date` date DEFAULT NULL,
  `no_alert` tinyint(1) NOT NULL DEFAULT '0',
  `ident_value` varchar(255) DEFAULT NULL,
  `grant_date` date DEFAULT NULL,
  `checker_person_id` int(10) unsigned NOT NULL,
  `checking_date` date NOT NULL,
  `checking_sentence` varchar(255) NOT NULL,
  `is_current_validity` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `validity_person_id_validity_type_id_registration_date_uindex` (`person_id`,`validity_type_id`,`registration_date`),
  KEY `validity_validity_type_id_fk` (`validity_type_id`),
  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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Table structure for table `validity_2_validity_page`
--
 
CREATE TABLE `validity_2_validity_page` (
  `validity_id` int(10) unsigned NOT NULL,
  `validity_page_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`validity_id`,`validity_page_id`),
  KEY `validity_2_validity_page_validity_page_id_fk` (`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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Table structure for table `validity_page`
--
 
CREATE TABLE `validity_page` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `validity_type_page_id` int(10) unsigned NOT NULL,
  `filename` varchar(255) NOT NULL,
  `file` mediumblob NOT NULL,
  `file_extension` varchar(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `validity_page_validity_type_page_id_fk` (`validity_type_page_id`),
  CONSTRAINT `validity_page_validity_type_page_id_fk` FOREIGN KEY (`validity_type_page_id`) REFERENCES `validity_type_page` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Table structure for table `validity_type`
--
 
CREATE TABLE `validity_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `time_limitation` tinyint(1) NOT NULL DEFAULT '0',
  `ident_value_enable` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `grant_date_enable` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `mandatory` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `experience_formula` text,
  `alert_on_login` int(10) DEFAULT '-2',
  `mandatory_access_control` tinyint(1) unsigned DEFAULT '0',
  `first_reminder_alert` int(10) unsigned DEFAULT '0',
  `reminder_frequency_alert` int(10) unsigned DEFAULT '0',
  `associate_attachment` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `certification_process` tinyint(1) NOT NULL DEFAULT '0',
  `is_contract` tinyint(1) NOT NULL DEFAULT '0',
  `contract_filename` varchar(255) NOT NULL,
  `contract_file` mediumblob NOT NULL,
  `contract_file_extension` varchar(4) NOT NULL,
  `is_OF_contract` tinyint(1) NOT NULL DEFAULT '0',
  `activated` tinyint(1) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='QUALIFICATIONS LIST';
 
--
-- Table structure for table `validity_type_page`
--
 
CREATE TABLE `validity_type_page` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `validity_type_id` int(10) unsigned NOT NULL,
  `page_index` int(10) unsigned NOT NULL,
  `label` varchar(40) NOT NULL,
  `is_mandatory` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `validity_type_page_validity_type_id_page_index_uindex` (`validity_type_id`,`page_index`),
  CONSTRAINT `validity_type_page_validity_type_id_fk` FOREIGN KEY (`validity_type_id`) REFERENCES `validity_type` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Table structure for table `variable`
--
 
CREATE TABLE `variable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `variable` varchar(255) DEFAULT NULL,
  `label` varchar(255) DEFAULT NULL,
  `category` tinyint(1) unsigned NOT NULL,
  `value_type` varchar(255) DEFAULT NULL,
  `order_num` int(11) DEFAULT 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) DEFAULT '0.00',
  `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) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;</sql>


=Description=
=Description=

Revision as of 07:42, 27 August 2020

Structure

<sql>create table account (

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

)

   comment 'List of account';

create index idx_owner_id

   on account (owner_id);

create table account_entry (

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

Description

Table booking

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

Table flight

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

Champs cachés

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

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


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

SELECT hash_password FROM person;

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