Structure de la base de données : Différence entre versions
(→Champs cachés) |
(→Structure) |
||
(18 révisions intermédiaires par 2 utilisateurs non affichées) | |||
Ligne 1 : | Ligne 1 : | ||
+ | =[[Media:OpenFlyers_database_schema.png|Schéma de la structure de la base de données]]= | ||
+ | |||
=Structure= | =Structure= | ||
Ligne 24 : | Ligne 26 : | ||
on account (owner_id); | on account (owner_id); | ||
− | + | CREATE TABLE `account_entry` ( | |
− | ( | + | `id` int(11) NOT NULL AUTO_INCREMENT, |
− | + | `flow_id` int(11) DEFAULT NULL, | |
− | + | `account_date` datetime DEFAULT NULL, | |
− | + | `account_id` int(11) DEFAULT NULL, | |
− | + | `credit` decimal(15,5) DEFAULT '0.00000', | |
− | + | `debit` decimal(15,5) DEFAULT '0.00000', | |
− | + | `payment_type` int(10) unsigned DEFAULT NULL, | |
− | + | `payment_description` text, | |
− | + | `person_delivery_id` int(10) unsigned DEFAULT NULL, | |
− | + | `comments` text, | |
− | + | `validated` int(1) DEFAULT '0', | |
− | + | `exported` int(1) DEFAULT '0', | |
− | + | `registration_date` datetime DEFAULT NULL, | |
− | + | `budget_id` int(10) unsigned DEFAULT NULL, | |
− | + | `product_id` int(10) unsigned DEFAULT NULL, | |
− | + | `signature` varchar(56) DEFAULT NULL, | |
− | + | `signature_date` bigint(13) unsigned DEFAULT NULL, | |
− | + | `lettering` int(11) DEFAULT NULL, | |
− | + | `lettering_date` datetime DEFAULT NULL, | |
− | + | `flow_reversal_id` int(10) unsigned DEFAULT NULL, | |
− | + | `transaction_id` text, | |
− | ) | + | PRIMARY KEY (`id`), |
− | + | KEY `idx_flow_id` (`flow_id`), | |
− | + | KEY `idx_account_date` (`account_date`), | |
− | + | KEY `idx_signature_date` (`signature_date`), | |
− | + | KEY `flow_reversal_id_idx` (`flow_reversal_id`) | |
− | + | ); | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
create table account_link | create table account_link | ||
Ligne 101 : | Ligne 98 : | ||
profile_id bigint unsigned default 0 not null, | profile_id bigint unsigned default 0 not null, | ||
primary key (accounting_id, profile_id) | primary key (accounting_id, profile_id) | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
); | ); | ||
Ligne 267 : | Ligne 250 : | ||
resource_id int unsigned default 0 not null, | resource_id int unsigned default 0 not null, | ||
primary key (booking_id, resource_id) | primary key (booking_id, resource_id) | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE `booking_training_program` ( | ||
+ | `booking_group_id` int(10) unsigned NOT NULL, | ||
+ | `training_program_id` int(10) unsigned NOT NULL, | ||
+ | PRIMARY KEY (`booking_group_id`,`training_program_id`), | ||
+ | CONSTRAINT `booking_training_program_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) | ||
); | ); | ||
Ligne 287 : | Ligne 277 : | ||
); | ); | ||
− | + | CREATE TABLE `business_field` ( | |
− | ( | + | `id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
− | + | `variable` varchar(255) DEFAULT NULL, | |
− | + | `label` varchar(255) NOT NULL, | |
− | + | `value_type` varchar(40) NOT NULL, | |
− | + | `category` varchar(255) NOT NULL, | |
− | + | `order_num` int(11) NOT NULL, | |
− | + | `dsn` varchar(20) DEFAULT 'customer', | |
− | + | `compulsory_fill` tinyint(1) DEFAULT '0', | |
− | + | `linked_category` varchar(255) DEFAULT NULL, | |
− | + | `linked_business_field_id` int(10) unsigned DEFAULT NULL, | |
− | + | `linked_field_name` varchar(255) DEFAULT NULL, | |
− | + | `max_display` int(10) DEFAULT '-1', | |
− | + | `formula` text, | |
− | + | `default_value` text, | |
− | + | `placeholder` text, | |
− | + | `access_level_required` tinyint(4) NOT NULL DEFAULT '0', | |
− | + | `user_access_mode` tinyint(1) NOT NULL DEFAULT '0', | |
− | + | PRIMARY KEY (`id`), | |
− | + | UNIQUE KEY `variable` (`variable`), | |
− | + | KEY `business_field_linked_business_field_id_fk` (`linked_business_field_id`), | |
− | ) | + | CONSTRAINT `business_field_linked_business_field_id_fk` FOREIGN KEY (`linked_business_field_id`) REFERENCES `business_field` (`id`) |
− | + | ) COMMENT='List of extra form field'; | |
create table business_field_activity_type | create table business_field_activity_type | ||
Ligne 648 : | Ligne 638 : | ||
); | ); | ||
− | + | CREATE TABLE `flight_track` ( | |
− | ( | + | `id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
− | + | `flight_id` int(10) NOT NULL, | |
− | + | `track` mediumblob NOT NULL, | |
− | + | `file_type` varchar(255) DEFAULT 'kml', | |
− | + | PRIMARY KEY (`id`) | |
); | ); | ||
Ligne 1 006 : | Ligne 996 : | ||
primary key (code, key_id) | primary key (code, key_id) | ||
); | ); | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
create table payment_summary_file | create table payment_summary_file | ||
Ligne 1 030 : | Ligne 1 009 : | ||
); | ); | ||
− | + | CREATE TABLE `payment_type` ( | |
− | ( | + | `id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
− | + | `name` text, | |
− | + | `text_field_label` text, | |
− | + | `only_admin` tinyint(1) unsigned NOT NULL DEFAULT '0', | |
− | + | `pos_key_id` int(10) unsigned DEFAULT NULL, | |
− | + | `treasury_account_id` int(10) unsigned DEFAULT NULL, | |
− | + | `person_delivery` tinyint(1) unsigned DEFAULT '0', | |
− | + | `person_budget_id` int(10) unsigned DEFAULT NULL, | |
− | ) | + | `treasury_budget_id` int(10) unsigned DEFAULT NULL, |
− | + | `minimum_amount` decimal(10,0) DEFAULT NULL, | |
+ | `maximum_amount` decimal(10,0) DEFAULT NULL, | ||
+ | PRIMARY KEY (`id`) | ||
+ | ) COMMENT='type of payment description'; | ||
create table person | create table person | ||
Ligne 1 587 : | Ligne 1 569 : | ||
primary key, | primary key, | ||
name varchar(255) not null | name varchar(255) not null | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE `training` ( | ||
+ | `id` int(10) unsigned NOT NULL AUTO_INCREMENT, | ||
+ | `name` varchar(255) NOT NULL, | ||
+ | PRIMARY KEY (`id`) | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE `training_activity` ( | ||
+ | `activity_id` int(10) unsigned NOT NULL, | ||
+ | `training_item_id` int(10) unsigned NOT NULL DEFAULT '0', | ||
+ | `training_program_id` int(10) unsigned NOT NULL DEFAULT '1', | ||
+ | `level_reached` int(10) unsigned DEFAULT NULL, | ||
+ | `comment` varchar(255) DEFAULT NULL, | ||
+ | `validated` tinyint(1) NOT NULL DEFAULT '0', | ||
+ | PRIMARY KEY (`activity_id`,`training_item_id`,`training_program_id`), | ||
+ | KEY `training_program_id_idx` (`training_program_id`), | ||
+ | KEY `training_program_item_id_idx` (`training_program_id`,`training_item_id`), | ||
+ | CONSTRAINT `training_activity_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, | ||
+ | CONSTRAINT `training_activity_training_program_item_id_fk` FOREIGN KEY (`training_program_id`, `training_item_id`) REFERENCES `training_program_item` (`training_program_id`, `training_item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE `training_activity_next_item` ( | ||
+ | `student_id` int(10) unsigned NOT NULL, | ||
+ | `training_program_id` int(10) unsigned NOT NULL, | ||
+ | `training_item_id` int(10) unsigned NOT NULL, | ||
+ | `reporting_offset` tinyint(10) unsigned NOT NULL DEFAULT '0', | ||
+ | `item_preselected` tinyint(1) unsigned NOT NULL DEFAULT '0', | ||
+ | PRIMARY KEY (`student_id`,`training_program_id`,`training_item_id`), | ||
+ | KEY `training_item_id_idx` (`training_item_id`), | ||
+ | KEY `training_program_id_idx` (`training_program_id`), | ||
+ | KEY `training_activity_next_item_training_program_item_id_fk` (`training_program_id`,`training_item_id`), | ||
+ | CONSTRAINT `training_activity_next_item_student_id_fk` FOREIGN KEY (`student_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, | ||
+ | CONSTRAINT `training_activity_next_item_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, | ||
+ | CONSTRAINT `training_activity_next_item_training_program_item_id_fk` FOREIGN KEY (`training_program_id`, `training_item_id`) REFERENCES `training_program_item` (`training_program_id`, `training_item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE `training_activity_person` ( | ||
+ | `activity_id` int(10) unsigned NOT NULL, | ||
+ | `person_id` int(10) unsigned NOT NULL, | ||
+ | `num` int(10) unsigned NOT NULL DEFAULT '0', | ||
+ | `training_checking_date` datetime DEFAULT NULL, | ||
+ | `training_checking_sentence` varchar(255) DEFAULT NULL, | ||
+ | PRIMARY KEY (`activity_id`,`person_id`,`num`), | ||
+ | KEY `person_id_idx` (`person_id`), | ||
+ | CONSTRAINT `training_activity_person_activity_id_fk` FOREIGN KEY (`activity_id`) REFERENCES `flight` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, | ||
+ | CONSTRAINT `training_activity_person_person_id_fk` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE `training_phase` ( | ||
+ | `training_program_item_order_num` int(11) unsigned NOT NULL, | ||
+ | `training_program_id` int(10) unsigned NOT NULL, | ||
+ | `name` varchar(255) DEFAULT NULL, | ||
+ | PRIMARY KEY (`training_program_item_order_num`,`training_program_id`), | ||
+ | KEY `training_program_id_idx` (`training_program_id`), | ||
+ | CONSTRAINT `training_phase_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE `training_program` ( | ||
+ | `id` int(10) unsigned NOT NULL AUTO_INCREMENT, | ||
+ | `training_id` int(10) unsigned DEFAULT NULL, | ||
+ | `name` varchar(255) NOT NULL, | ||
+ | PRIMARY KEY (`id`), | ||
+ | KEY `training_id_idx` (`training_id`), | ||
+ | CONSTRAINT `training_program_training_id_fk` FOREIGN KEY (`training_id`) REFERENCES `training` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE `training_program_activity` ( | ||
+ | `id` int(10) unsigned NOT NULL AUTO_INCREMENT, | ||
+ | `training_program_id` int(10) unsigned NOT NULL, | ||
+ | `activity_type_id` bigint(20) unsigned NOT NULL, | ||
+ | `duration` int(11) unsigned DEFAULT NULL, | ||
+ | `breaktime` int(11) unsigned DEFAULT NULL, | ||
+ | `order_num` int(11) unsigned NOT NULL, | ||
+ | PRIMARY KEY (`id`), | ||
+ | KEY `training_program_id_idx` (`training_program_id`), | ||
+ | KEY `activity_type_id_idx` (`activity_type_id`), | ||
+ | CONSTRAINT `training_program_activity_activity_type_id_fk` FOREIGN KEY (`activity_type_id`) REFERENCES `activity_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, | ||
+ | CONSTRAINT `training_program_activity_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION | ||
+ | ); | ||
+ | |||
+ | CREATE TABLE `training_item` ( | ||
+ | `training_id` int(10) unsigned NOT NULL, | ||
+ | `training_item_label_id` int(10) unsigned NOT NULL, | ||
+ | `number_of_sessions` int(11) NOT NULL, | ||
+ | `order_num` int(11) NOT NULL AUTO_INCREMENT, | ||
+ | PRIMARY KEY (`training_id`,`training_item_label_id`), | ||
+ | KEY `order_num_idx` (`order_num` ASC), | ||
+ | CONSTRAINT `training_item_training_id_fk` FOREIGN KEY (`training_id`) REFERENCES `training` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION | ||
); | ); | ||
Ligne 2 044 : | Ligne 2 115 : | ||
==Champs cachés== | ==Champs cachés== | ||
− | Les champs suivants ne peuvent être exportés et ne doivent pas être inclus dans une requête SELECT. De plus, un "SELECT *" | + | Les champs suivants ne peuvent être exportés et ne doivent pas être inclus dans une requête SELECT. De plus, un "SELECT *" est interdit quand une des tables contient un champ caché : |
− | *Table journal: Tous les champs | + | *Table journal : Tous les champs |
− | *Table log: Tous les champs | + | *Table log : Tous les champs |
− | *Table parameter: Tous les champs | + | *Table parameter : Tous les champs |
− | *Table person: | + | *Table person : |
**hash_password | **hash_password | ||
*Table structure : | *Table structure : | ||
− | ** | + | **address |
+ | **admin_num | ||
+ | **city | ||
+ | **country | ||
+ | **default_notification | ||
+ | **default_slot_range | ||
+ | **default_timezone | ||
+ | **default_view_type | ||
+ | **email | ||
+ | **fax | ||
+ | **first_hour_displayed | ||
**info_cell | **info_cell | ||
+ | **lang | ||
+ | **last_hour_displayed | ||
**logo | **logo | ||
**logo_name | **logo_name | ||
**logo_ext | **logo_ext | ||
**logo_size | **logo_size | ||
− | ** | + | **mail_from_address |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
**mailing_list_name | **mailing_list_name | ||
**mailing_list_type | **mailing_list_type | ||
− | ** | + | **min_slot_range |
− | ** | + | **name |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
**phone | **phone | ||
− | ** | + | **state |
− | ** | + | **twilight_range |
− | ** | + | **usual_profiles |
**welcome_cell | **welcome_cell | ||
+ | **zipcode | ||
+ | *Table oauth_client : Tous les champs | ||
+ | *Table oauth_access_token : Tous les champs | ||
+ | *Table oauth_auth_code : Tous les champs | ||
+ | *Table oauth_refresh_token : Tous les champs | ||
Exemple de requêtes non autorisées : | Exemple de requêtes non autorisées : |
Version actuelle en date du 10 janvier 2024 à 16:25
Sommaire
Schéma de la structure de la base de données
Structure
create table account ( id int unsigned auto_increment primary key, creation_date date null, name text null, export_account varchar(255) null, activated tinyint(1) unsigned default 1 not null, category tinyint(1) unsigned default 0 null, account_type tinyint(1) unsigned default 0 null, owner_id int unsigned default 0 not null, payment_allowed tinyint(1) default 0 null, budget_id int unsigned null, order_num int null, accounting_id int unsigned null, deactivated_date datetime null, group_sales tinyint(1) default 0 not null ) comment 'List of account'; create index idx_owner_id on account (owner_id); CREATE TABLE `account_entry` ( `id` int(11) NOT NULL AUTO_INCREMENT, `flow_id` int(11) DEFAULT NULL, `account_date` datetime DEFAULT NULL, `account_id` int(11) DEFAULT NULL, `credit` decimal(15,5) DEFAULT '0.00000', `debit` decimal(15,5) DEFAULT '0.00000', `payment_type` int(10) unsigned DEFAULT NULL, `payment_description` text, `person_delivery_id` int(10) unsigned DEFAULT NULL, `comments` text, `validated` int(1) DEFAULT '0', `exported` int(1) DEFAULT '0', `registration_date` datetime DEFAULT NULL, `budget_id` int(10) unsigned DEFAULT NULL, `product_id` int(10) unsigned DEFAULT NULL, `signature` varchar(56) DEFAULT NULL, `signature_date` bigint(13) unsigned DEFAULT NULL, `lettering` int(11) DEFAULT NULL, `lettering_date` datetime DEFAULT NULL, `flow_reversal_id` int(10) unsigned DEFAULT NULL, `transaction_id` text, PRIMARY KEY (`id`), KEY `idx_flow_id` (`flow_id`), KEY `idx_account_date` (`account_date`), KEY `idx_signature_date` (`signature_date`), KEY `flow_reversal_id_idx` (`flow_reversal_id`) ); create table account_link ( account_id int unsigned default 0 not null, linked_account_id int unsigned default 0 not null, primary key (account_id, linked_account_id) ); create table account_type ( id int unsigned auto_increment primary key, name varchar(255) null, category tinyint(1) unsigned default 0 null, activated tinyint(1) default 1 null, order_num int null, accounting_id int unsigned null, export_account_pattern varchar(255) null ); create table account_type_profile ( account_type_id int unsigned default 0 not null, profile_id bigint unsigned default 0 not null, primary key (account_type_id, profile_id) ) comment 'Linked account type and profile'; create table accounting ( id int unsigned auto_increment primary key, name varchar(255) null, unit_name varchar(255) null, symbol varchar(255) null, format tinyint(1) null, decimal_precision int unsigned default 2 null ); create table accounting_profile ( accounting_id int unsigned default 0 not null, profile_id bigint unsigned default 0 not null, primary key (accounting_id, profile_id) ); create table activity_type ( id bigint unsigned default 0 not null primary key, name varchar(255) null, order_num int null, activated tinyint(1) default 1 not null, color int unsigned null, training tinyint(1) default 0 not null ) comment 'list of flight type'; create table aircraft ( id int unsigned default 0 not null primary key, ref_date datetime null, ref_hours int null, inspection_date datetime null, inspection_time int null, tolerance_time int(11) unsigned default 6000 null, last_counter int null, interval_visit int(11) unsigned default 30000 null, time_alert1 int default 6000 null, time_alert2 int default 0 null, time_alert3 int default -3000 null, day_alert1 smallint(3) default 15 null, day_alert2 smallint(3) default 0 null, day_alert3 smallint(3) default -8 null ); create table aircraft_type ( id int unsigned auto_increment primary key, flight_time_formula varchar(255) default '%DURATION' null, counter_state tinyint(2) default -1 not null, tolerance int default 0 null, autonomy int default 5990 null, true_air_speed int unsigned default 0 not null, digit_counter_number tinyint(1) unsigned default 4 not null ) comment 'Types of aircraft'; create table aircraft_type_allowed_status ( aircraft_type_id int unsigned null, place_num int unsigned null, status_id int unsigned null ) comment 'List of allowed functions for each aircraft type'; create table aircraft_type_mandatory_flight_type ( aircraft_type_id int unsigned null, activity_type_id bigint unsigned null ) comment 'List of compulsory flight type for each aircraft type'; create table aircraft_type_uncomp_flight_type ( aircraft_type_id int unsigned null, activity_type_id bigint unsigned null ) comment 'List of uncompatible flight type for each aircraft type'; create table aircraft_type_validity_type ( aircraft_type_id int unsigned default 0 not null, validity_type_id int unsigned default 0 not null, check_num int unsigned default 0 not null, primary key (aircraft_type_id, validity_type_id, check_num) ) comment 'Types of qualif required for each aircraft type'; create table allocation_rule ( id int unsigned auto_increment primary key, title varchar(255) not null, account_id int(10) not null, keyword varchar(255) null, order_num int(10) not null, amount tinyint(1) not null, mandatory tinyint(1) default 0 not null ); create table balance ( account_id int unsigned not null, balance_date_id int unsigned not null, debit decimal(15, 5) default 0.00000 null, credit decimal(15, 5) default 0.00000 null, primary key (account_id, balance_date_id) ); create table balance_date ( id int unsigned auto_increment primary key, balance_date datetime null ); create table booking ( id int unsigned auto_increment primary key, booking_group_id int unsigned null, start_date datetime null, end_date datetime null ); create table booking_account_entry ( booking_id int unsigned default 0 not null, account_entry_flow_id int unsigned default 0 not null, primary key (booking_id, account_entry_flow_id) ); create table booking_activity_type ( booking_id int unsigned default 0 not null, activity_type_id int unsigned default 0 not null, primary key (booking_id, activity_type_id) ); create table booking_changelog ( id int unsigned auto_increment primary key, booking_id int unsigned not null, person_id int unsigned not null, changelog_date datetime not null, booking_state text null ); create table booking_person ( booking_id int unsigned default 0 not null, person_id int unsigned default 0 not null, place_num int unsigned null, status_id int unsigned null, primary key (booking_id, person_id) ); create table booking_resource ( booking_id int unsigned default 0 not null, resource_id int unsigned default 0 not null, primary key (booking_id, resource_id) ); CREATE TABLE `booking_training_program` ( `booking_group_id` int(10) unsigned NOT NULL, `training_program_id` int(10) unsigned NOT NULL, PRIMARY KEY (`booking_group_id`,`training_program_id`), CONSTRAINT `booking_training_program_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ); create table budget ( id int unsigned auto_increment primary key, group_id int unsigned default 0 not null, name varchar(255) null, order_num int null, export_code varchar(255) null ); create table budget_group ( id int unsigned auto_increment primary key, name varchar(255) null, order_num int null ); CREATE TABLE `business_field` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `variable` varchar(255) DEFAULT NULL, `label` varchar(255) NOT NULL, `value_type` varchar(40) NOT NULL, `category` varchar(255) NOT NULL, `order_num` int(11) NOT NULL, `dsn` varchar(20) DEFAULT 'customer', `compulsory_fill` tinyint(1) DEFAULT '0', `linked_category` varchar(255) DEFAULT NULL, `linked_business_field_id` int(10) unsigned DEFAULT NULL, `linked_field_name` varchar(255) DEFAULT NULL, `max_display` int(10) DEFAULT '-1', `formula` text, `default_value` text, `placeholder` text, `access_level_required` tinyint(4) NOT NULL DEFAULT '0', `user_access_mode` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `variable` (`variable`), KEY `business_field_linked_business_field_id_fk` (`linked_business_field_id`), CONSTRAINT `business_field_linked_business_field_id_fk` FOREIGN KEY (`linked_business_field_id`) REFERENCES `business_field` (`id`) ) COMMENT='List of extra form field'; create table business_field_activity_type ( business_field_id int unsigned default 0 not null, activity_type_id bigint unsigned default 0 not null, business_field_group_id int unsigned default 0 not null, visibility_type tinyint(1) unsigned null, primary key (business_field_id, activity_type_id) ); create table business_field_content ( category_id int unsigned not null, business_field_id int unsigned default 0 not null, person_id int unsigned default 0 not null, content text null, placeholder text null, primary key (category_id, business_field_id, person_id) ) comment 'Content of extra form field'; create table business_field_group ( id int unsigned auto_increment primary key, label varchar(255) null, order_num int null ); create table criteria ( id int auto_increment primary key, label varchar(255) not null, predicate text not null, order_num int null, dsn varchar(20) default 'customer' null ); create table criteria_profile ( criteria_id int unsigned not null, profile_id bigint unsigned not null, primary key (criteria_id, profile_id) ); create table customer_bill_entry ( id int unsigned auto_increment primary key, flow_id int unsigned null, account_entry_id int unsigned null, account_entry_flow_id int unsigned null, owner_category int unsigned null, owner_id int unsigned null, customer_bill_id int unsigned null, product_id int unsigned null, qty decimal(15, 2) default 0.00 null, unit_price decimal(15, 5) default 0.00000 null, debit decimal(15, 5) default 0.00000 null, credit decimal(15, 5) default 0.00000 null, bill_date datetime null ); create index idx_account_entry_flow_id on customer_bill_entry (account_entry_flow_id); create index idx_account_entry_id on customer_bill_entry (account_entry_id); create table customer_bill_file ( id int unsigned auto_increment primary key, file_type varchar(255) null, content longblob null ) comment 'List of customer bill stored file'; create table customer_receipt_entry ( id int unsigned auto_increment primary key, flow_id int unsigned null, account_entry_id int unsigned null, account_entry_flow_id int unsigned null, owner_category int unsigned null, owner_id int unsigned null, payment_type_id int unsigned null, customer_receipt_id int unsigned null, debit decimal(15, 5) default 0.00000 null, credit decimal(15, 5) default 0.00000 null, receipt_date datetime null, owner_email varchar(255) null ); create index idx_account_entry_flow_id on customer_receipt_entry (account_entry_flow_id); create index idx_account_entry_id on customer_receipt_entry (account_entry_id); create table customer_receipt_file ( id int unsigned auto_increment primary key, file_type varchar(255) null, content longblob null ); create table default_display ( person_id int unsigned not null, display_key varchar(255) default '' not null, display_value text null, primary key (person_id, display_key) ); create table default_multi_display ( resource_type char(30) not null, person_id int unsigned not null, resource_id varchar(100) not null, display_value text null, primary key (resource_type, person_id, resource_id) ); create table email_sent ( id bigint unsigned auto_increment primary key, message_id varchar(150) not null, sender varchar(255) not null, title varchar(255) not null, category tinyint unsigned not null, category_id int unsigned not null, constraint message_id_UNIQUE unique (message_id) ); create table email_sent_recipient ( id bigint unsigned auto_increment primary key, email_sent_id bigint unsigned not null, person_id int unsigned not null, email varchar(255) null, recipient_type tinyint unsigned null, smtp_status_category varchar(30) null, email_status tinyint unsigned null, update_date datetime not null ); create index email_sent_id_idx on email_sent_recipient (email_sent_id); create table exceptionnal_inst_date ( id int unsigned auto_increment primary key, person_id int unsigned null, start_date datetime null, end_date datetime null, presence tinyint(1) unsigned null ); create table extra_field_profile ( business_field_id int unsigned default 0 not null, booking_popup_display_4_profile_id bigint unsigned default 0 not null, primary key (business_field_id, booking_popup_display_4_profile_id) ) comment 'Linked extra field and profile'; create table facebook ( id int unsigned auto_increment primary key, category int unsigned not null, owner_id int unsigned not null, small blob null, original mediumblob not null, label text null, description text null ); create table favorite_icao ( icao varchar(6) default '' not null primary key ) comment 'favorite airfield list'; create table favorite_report ( report_id int unsigned not null primary key ); create table favorite_report_business_field ( report_id int unsigned not null, business_field_id int unsigned not null, default_value text null, primary key (report_id, business_field_id) ); create table favorite_report_profile ( report_id int unsigned not null, profile_id bigint unsigned default 0 not null, primary key (report_id, profile_id) ); create table fhp_aircraft_type ( fhp_id int(10) default 0 not null, aircraft_type_id int(10) default 0 not null, primary key (fhp_id, aircraft_type_id) ) comment 'flight hour pricing aircraft type list'; create table fhp_flight_type ( fhp_id int(10) default 0 not null, activity_type_id bigint unsigned default 0 not null, excluded tinyint(1) unsigned default 0 null, primary key (fhp_id, activity_type_id) ) comment 'flight hour pricing flight type list'; create table fhp_profile ( fhp_id int unsigned not null, profile_id bigint unsigned not null, place_num tinyint(1) unsigned default 0 not null, primary key (fhp_id, profile_id, place_num) ); create table file ( id int unsigned auto_increment primary key, name varchar(255) null, content longblob null, file_type varchar(255) null, category tinyint(1) unsigned null, owner_id int(10) null, file_group_id int(10) null ) comment 'List of stored file'; create table file_group ( id int unsigned auto_increment primary key, label varchar(255) null ); create table flight ( id int unsigned auto_increment primary key, aircraft_id int unsigned null, start_date datetime null, duration int null, activity_type_id bigint unsigned null, people_onboard int unsigned null, departure_location_id int unsigned null, arrival_location_id int unsigned null, counter_departure int unsigned null, counter_arrival int unsigned null, landing_number int(10) null, airborne tinyint(1) unsigned null, validated tinyint unsigned default 0 null, departure_icao_id varchar(6) null, arrival_icao_id varchar(6) null ) comment 'list of flight'; create table flight_account_entry ( flight_id int unsigned default 0 not null, account_entry_id int unsigned default 0 not null, primary key (flight_id, account_entry_id) ) comment 'flight account entry join'; create index idx_account_entry_id on flight_account_entry (account_entry_id); create table flight_hours_pricing ( id int unsigned auto_increment primary key, name text null, price_formula text null, left_account_id int unsigned null, right_account_id int unsigned null, left_account_type tinyint(1) unsigned default 0 null, right_account_type tinyint(1) unsigned default 0 null, order_num int null, credit_budget_id int unsigned null, debit_budget_id int unsigned null, product_id int unsigned null, sale_trigger_id int unsigned default 0 null, query text null, variable_formula text null, business_field_id int unsigned null, debit_bill_num int unsigned null, credit_bill_num int unsigned null, qty_formula text null, unit_price_formula text null ) comment 'list of flight hours pricing formula'; create table flight_pilot ( flight_id int unsigned not null, pilot_id int unsigned not null, status_id int unsigned null, num int unsigned default 0 not null, primary key (flight_id, pilot_id, num) ) comment 'list of crew for each flight'; create table flight_tank_qty ( id int unsigned auto_increment primary key, flight_id int unsigned not null, tank_id int unsigned not null, quantity varchar(255) default '0.00' not null, after_flight tinyint(1) default 0 not null, account_id int unsigned null, pay_type tinyint(1) unsigned null ); CREATE TABLE `flight_track` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `flight_id` int(10) NOT NULL, `track` mediumblob NOT NULL, `file_type` varchar(255) DEFAULT 'kml', PRIMARY KEY (`id`) ); create table flight_type_mandatory_validity_type ( activity_type_id bigint unsigned default 0 not null, validity_type_id int unsigned default 0 not null, primary key (activity_type_id, validity_type_id) ) comment 'list of mandatory qualification for each flight type'; create table import ( id int unsigned auto_increment primary key, label varchar(255) not null, order_num int null, import_file_type varchar(255) default 'csv-comma-CRLF' not null, match_query text null, uptodate_test_query text null, update_query text null, update_activated tinyint(1) default 0 not null, login varchar(255) null, hash_password varchar(255) null, sync_task_name varchar(255) null ) comment 'List of import'; create table ip_stopped ( id int unsigned auto_increment primary key, ip varchar(255) not null, counter tinyint(1) unsigned not null, expire_date datetime not null ) comment 'BLACKLISTED IP'; create table journal ( id int unsigned auto_increment primary key, login varchar(255) null, date_log datetime null, rights text null, rights2 text null, action varchar(255) null, person_id int unsigned default 0 null ) comment 'list of logs'; create index idx_action on journal (action); create index idx_date_log on journal (date_log); create table key_alert ( id int unsigned auto_increment primary key, alert_date datetime null, status int unsigned default 0 not null ) comment 'Key alerts'; create table key_assignment ( key_id tinyint(2) unsigned default 0 not null primary key, key_name tinytext null, aircraft_id int unsigned default 0 null, key_state tinyint(1) unsigned default 0 null, key_word bigint unsigned default 0 null ); create table key_host ( id tinyint(2) auto_increment primary key, timeout tinyint(2) default 10 not null, num_key tinyint(2) unsigned default 10 not null, ipkey varchar(50) default '127.0.0.1' not null, httpport int default 4080 not null ); create table key_log ( id int unsigned auto_increment primary key, log_date datetime null, action varchar(255) null, message varchar(255) null, key_id int unsigned null, person_id int unsigned null, xmlrpc int unsigned not null, error varchar(255) null ) comment 'Key logs'; create table location ( icao_name varchar(6) not null primary key, name varchar(64) not null, latitude double null, longitude double null, altitude int(7) null, weather_station int(1) unsigned null, asked_counter bigint default 0 not null ) comment 'airfields coord'; create table log ( journal_id int unsigned not null, action varchar(255) null, table_name varchar(255) null, field_name varchar(255) null, field_value varchar(255) null ) comment 'part of logs'; create index idx_field_value on log (field_value); create index idx_journal_id on log (journal_id); create index journal_id on log (journal_id); create table logger ( id int unsigned auto_increment primary key, serial_number int unsigned null, color varchar(20) default 'red' not null, activated tinyint(1) unsigned default 1 not null, order_num int unsigned not null ) comment 'Logger parameters'; create table login_stopped ( id int unsigned auto_increment primary key, login varchar(255) not null, counter tinyint unsigned not null, expire_date datetime not null ) comment 'BLACKLISTED LOGIN'; create table m_component ( id int unsigned auto_increment primary key, parent_id int unsigned default 0 not null, m_component_type_id int unsigned null, resource_id int unsigned null, order_num int not null, serial_number varchar(255) null, brandnew_date datetime null, activated tinyint(1) unsigned default 1 not null ); create table m_component_type ( id int unsigned auto_increment primary key, parent_id int unsigned default 0 not null, resource_type_id int null, m_classification_id int null, is_maintenance_check tinyint(1) unsigned default 1 not null, order_num int not null, label varchar(255) null, description varchar(255) null, manufacturer varchar(255) null, manufacturer_reference varchar(255) null, part_number varchar(255) null, periodicity int unsigned null, tolerance int unsigned default 0 not null, calendar_periodicity int unsigned null, calendar_tolerance int unsigned default 0 not null, activated tinyint(1) unsigned default 1 not null ); create table m_component_type_parentality ( m_component_type_id int unsigned not null, m_component_type_parent_id int unsigned not null, primary key (m_component_type_id, m_component_type_parent_id) ); create table m_history ( id int unsigned auto_increment primary key, m_component_id int unsigned not null, install_date datetime null, remove_date datetime null, total_hours_on_install int unsigned default 0 not null, threshold_hours int unsigned default 0 not null, threshold_date datetime null, threshold_date_locked tinyint(1) unsigned default 0 not null, threshold_hours_locked tinyint(1) unsigned default 0 not null ); create table m_operation ( id int unsigned auto_increment primary key, content text null, m_component_type_id int unsigned not null, order_num int not null ); create table maintenance_history ( id int unsigned auto_increment primary key, resource_id int unsigned null, maintenance_program_id int unsigned null, frame_hours_at_work_start int(10) null, date_work_end date null, min_overhaul_counter int(10) null, max_overhaul_counter int(10) null, min_overhaul_date date null, max_overhaul_date date null, min_next_overhaul_counter int(10) null, max_next_overhaul_counter int(10) null, min_next_overhaul_date date null, max_next_overhaul_date date null, first_reference_visit tinyint(1) unsigned default 0 not null, is_visit tinyint(1) unsigned default 0 not null, action_type tinyint(1) unsigned null, previous_line_linked_time_action int(10) null, previous_line_linked_calendar_action int(10) null, next_line_linked_time_action int(10) null, next_line_linked_calendar_action int(10) null, time_tolerance int(10) null, calendar_tolerance int(10) null, time_periodicity_component int(10) null, calendar_periodicity_component int(10) null, time_periodicity int(10) null, calendar_periodicity int(10) null, reference_overhaul_counter int(10) null, reference_overhaul_date date null ); create table maintenance_program ( id int unsigned auto_increment primary key, resource_id int unsigned null, component_type_id int unsigned null, description varchar(255) null, time_periodicity int(10) null, calendar_periodicity int(10) null, time_first_reference int(10) null, calendar_first_reference date null, time_tolerance int(10) null, calendar_tolerance int(10) null, frame_hours_at_work_start int(10) null, date_work_end date null, time_elapsed int(10) null, calendar_elapsed int(10) null, time_remaining int(10) null, calendar_remaining int(10) null, min_next_overhaul_counter int(10) null, max_next_overhaul_counter int(10) null, min_next_overhaul_date date null, max_next_overhaul_date date null, effective_hours_at_work_start int(10) null, theoretical_overhaul_counter int(10) null, intelligent_overhaul_counter int(10) null, intelligent_min_next_overhaul_counter int(10) null, intelligent_max_next_overhaul_counter int(10) null, effective_date_at_work_end date null, theoretical_overhaul_date date null, intelligent_overhaul_date date null, intelligent_min_next_overhaul_date date null, intelligent_max_next_overhaul_date date null, overlapping_group_id int unsigned null, is_visit_rg tinyint(1) unsigned default 0 not null ); create table maintenance_view ( id int unsigned default 0 not null primary key, frame_total_time int(10) null, frame_time_since_rg int(10) null, engine_time_since_rg int(10) null, propeller_time_since_rg int(10) null, potential_remaining_next_time_action int(10) null, potential_remaining_next_time_visit int(10) null, potential_remaining_next_priority_time_action int(10) null, potential_remaining_next_calendar_action int(10) null, potential_remaining_next_calendar_visit int(10) null, potential_remaining_next_priority_calendar_action int(10) null, next_time_action_id int unsigned null, next_time_visit_id int unsigned null, next_priority_time_action_id int unsigned null, next_calendar_action_id int unsigned null, next_calendar_visit_id int unsigned null, next_priority_calendar_action_id int unsigned null, overhaul_counter_action int(10) null, overhaul_counter_visit int(10) null, overhaul_date_action date null, overhaul_date_visit date null ); create table map_logger_monitoring ( record_id int unsigned not null primary key, count_data int unsigned not null ) comment 'link between flight and track'; create table map_track ( id int unsigned auto_increment primary key, record_id int unsigned null, start_data int unsigned null, number int unsigned null, start_time datetime null, latitude_max double null, longitude_max double null, latitude_min double null, longitude_min double null, visible tinyint(1) unsigned default 1 not null ) comment 'Tracks split from GDR'; create table nationality ( code char(2) default '' not null primary key, label varchar(255) not null ); create table parameter ( code varchar(255) not null, key_id int unsigned default 0 not null, enabled tinyint(1) unsigned default 0 null, int_value int unsigned default 0 null, char_value text null, primary key (code, key_id) ); create table payment_summary_file ( id int unsigned auto_increment primary key, file_type varchar(255) null, content longblob null, record_date datetime null, total_entry int unsigned null, total_amount decimal(15, 5) unsigned null, payment_type_id int unsigned null ); CREATE TABLE `payment_type` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` text, `text_field_label` text, `only_admin` tinyint(1) unsigned NOT NULL DEFAULT '0', `pos_key_id` int(10) unsigned DEFAULT NULL, `treasury_account_id` int(10) unsigned DEFAULT NULL, `person_delivery` tinyint(1) unsigned DEFAULT '0', `person_budget_id` int(10) unsigned DEFAULT NULL, `treasury_budget_id` int(10) unsigned DEFAULT NULL, `minimum_amount` decimal(10,0) DEFAULT NULL, `maximum_amount` decimal(10,0) DEFAULT NULL, PRIMARY KEY (`id`) ) COMMENT='type of payment description'; create table person ( id int unsigned auto_increment primary key, name varchar(255) not null, hash_password varchar(255) not null, first_name varchar(255) null, last_name varchar(255) null, profile bigint unsigned default 0 not null, view_type int unsigned null, view_width tinyint unsigned default 12 not null, view_height tinyint(4) unsigned default 30 not null, aircrafts_viewed varchar(255) null, inst_viewed varchar(255) null, email varchar(255) null, timezone varchar(255) null, address varchar(255) null, zipcode varchar(255) null, city varchar(255) null, state varchar(255) null, country varchar(255) null, home_phone varchar(255) null, work_phone varchar(255) null, cell_phone varchar(255) null, lang varchar(255) null, notification tinyint unsigned null, activated tinyint(1) unsigned default 1 null, birthdate datetime default '0000-00-00 00:00:00' null, sex tinyint(1) unsigned default 0 not null, nationality char(2) null, total_flight_time int unsigned default 0 null, date_total_flight_time datetime null, guid varchar(255) null, activity_notification bigint unsigned null, constraint idx_name unique (name), constraint name_3 unique (name) ); create table person_awaiting_activation ( id int unsigned auto_increment primary key, login varchar(255) not null, ip varchar(255) not null, code varchar(255) not null, category varchar(255) not null, expiration_date datetime null, used tinyint(1) unsigned null ) comment 'member_awaiting_activation'; create table product ( id int unsigned auto_increment primary key, label varchar(255) not null, unit varchar(255) not null, free_sale tinyint(1) default 0 not null, locked tinyint(1) unsigned default 0 null, variable_id int unsigned null, sale_type tinyint(1) unsigned null ); create table profile ( id bigint unsigned default 0 not null primary key, name varchar(255) null, permits int unsigned null, permits2 int unsigned default 0 not null, permits3 int unsigned default 0 not null, pictogram int unsigned null, default_status_id int unsigned null ); create table profile_accounting_notification ( profile_id bigint unsigned default 0 not null, accounting_id int unsigned default 0 not null, primary key (profile_id, accounting_id) ); create table profile_extra_field_join ( profile_id bigint unsigned default 0 not null, business_field_id int unsigned default 0 not null, primary key (profile_id, business_field_id) ); create table profile_profile_view ( profile_id bigint unsigned not null, viewable_profile_id bigint unsigned not null, primary key (profile_id, viewable_profile_id) ); create table profile_required_account_type ( profile_id bigint unsigned default 0 not null, account_type_id int unsigned default 0 not null, primary key (profile_id, account_type_id) ); create table profile_resource_type_place ( profile_id bigint unsigned default 0 not null, resource_type_id int unsigned default 0 not null, place_num int unsigned not null, primary key (profile_id, resource_type_id, place_num) ); create table profile_resource_type_view ( profile_id bigint unsigned not null, viewable_resource_type_id int unsigned not null, primary key (profile_id, viewable_resource_type_id) ); create table profile_validity_type_join ( profile_id bigint unsigned default 0 not null, validity_type_id int unsigned default 0 not null, manage4oneself int(1) default 0 not null, certify int(1) default 0 not null, optional_contract tinyint(1) default 0 not null, primary key (profile_id, validity_type_id) ); create table profile_validity_type_notification ( profile_id bigint unsigned default 0 not null, validity_type_id int unsigned default 0 not null, primary key (profile_id, validity_type_id) ); create table psp_return ( id int unsigned auto_increment primary key, psp_transaction_id int unsigned null, bank_answer text null, bank_misc text null ); create table psp_transaction ( id int unsigned auto_increment primary key, credit_account_id int unsigned not null, debit_account_id int unsigned not null, transaction_date datetime not null, amount float not null, description varchar(255) null, payment_type_id int unsigned not null, state int unsigned default 0 not null, token text null ); create table psp_transaction_account_entry ( psp_transaction_id int unsigned default 0 not null, account_entry_flow_id int unsigned default 0 not null, primary key (psp_transaction_id, account_entry_flow_id) ); create table regular_presence_inst_date ( id int unsigned auto_increment primary key, person_id int unsigned null, start_day tinyint unsigned null, end_day tinyint unsigned null, start_hour time null, end_hour time null ); create table resource ( id int unsigned auto_increment primary key, name varchar(255) null, resource_type_id int unsigned null, comments varchar(255) null, order_num int null, activated tinyint(1) unsigned default 1 not null, bookable int unsigned default 1 null, physical int unsigned default 1 null, color int unsigned null ) comment 'List of resource'; create table resource_exceptional_availability ( id int unsigned auto_increment primary key, resource_id int unsigned null, start_date datetime null, end_date datetime null, presence tinyint(1) unsigned null ); create table resource_regular_availability ( id int unsigned auto_increment primary key, resource_id int unsigned null, start_day tinyint(1) unsigned null, end_day tinyint(1) unsigned null, start_hour time null, end_hour time null ); create table resource_type ( id int unsigned auto_increment primary key, name varchar(255) null, category int unsigned null, seats_available int default -1 null, comments varchar(255) null, order_num int null, activated tinyint(1) unsigned default 1 not null, max_booking_duration int(10) default -1 null, pictogram int unsigned null ) comment 'List of resource type'; create table resource_type_place_tag ( resource_type_id int unsigned default 0 not null, place_num tinyint(1) unsigned not null, place_tag varchar(255) null, place_quantity int(10) default -1 null, primary key (resource_type_id, place_num) ); create table sale_2_stock ( id int unsigned auto_increment primary key, product_id int unsigned null, stock_id int unsigned not null, stock_variation_qty_per_sale float default 0 not null ); create table sale_2_validity_type ( id int unsigned auto_increment primary key, validity_type_id int unsigned not null, new_formula varchar(255) null, update_formula varchar(255) null, product_id int unsigned null, constraint validity_type_id unique (validity_type_id) ) comment 'List of validity type into sale'; create index idx_product on sale_2_validity_type (product_id); create table sale_pricing ( id int unsigned auto_increment primary key, price_formula text null, debit_account_id int unsigned null, credit_account_id int unsigned null, debit_account_type tinyint(1) unsigned default 0 null, credit_account_type tinyint(1) unsigned default 0 null, credit_budget_id int unsigned null, debit_budget_id int unsigned null, order_num int null, label varchar(255) null, variable_formula varchar(255) null, debit_bill_num int unsigned null, credit_bill_num int unsigned null, qty_formula text null, unit_price_formula text null, added_product_id int unsigned null ); create table sale_pricing_product ( sale_pricing_id int unsigned default 0 not null, product_id int unsigned default 0 not null, primary key (sale_pricing_id, product_id) ); create table sale_pricing_profile ( sale_pricing_id int unsigned not null, profile_id bigint unsigned default 0 not null, primary key (sale_pricing_id, profile_id) ); create table sale_trigger ( id int(10) auto_increment primary key, name varchar(255) null, event varchar(255) null, locked tinyint(1) unsigned default 0 not null, query text null, validate_entry tinyint(1) default 0 null, group_sales tinyint(1) default 0 null ) comment 'List of sale trigger'; create table spreadsheet_parameter ( file_number int(10) not null, spreadsheet_key varchar(255) not null, spreadsheet_value varchar(255) not null, file_name varchar(255) null, google_url varchar(255) not null, file_type int unsigned null, primary key (file_number, spreadsheet_key, spreadsheet_value) ); create table statistic ( name varchar(255) not null, of_version tinyint(3) default 0 not null, value decimal(15, 2) default 0.00 not null, primary key (name, of_version) ); create table status ( id int unsigned auto_increment primary key, abbrev varchar(255) null, name varchar(255) null, pictogram int unsigned null ) comment 'List of functions for pilot'; create table stock ( id int unsigned auto_increment primary key, label varchar(255) not null, stock_type_id int unsigned not null ); create table stock_level ( id int unsigned auto_increment primary key, stock_id int unsigned not null, qty float default 0 not null, stock_date datetime null ); create table stock_type ( id int unsigned auto_increment primary key, label varchar(255) not null, unit varchar(255) not null ); create table stock_variation ( id int unsigned auto_increment primary key, stock_id int unsigned not null, qty float default 0 not null, variation_date datetime null, validated tinyint(1) unsigned default 0 null ); create table stock_variation_account_entry ( product_id int unsigned default 0 not null, stock_variation_id int unsigned not null, account_entry_flow_id int unsigned not null, person_id int unsigned not null, primary key (product_id, stock_variation_id, account_entry_flow_id) ); create index idx_account_entry_flow_id on stock_variation_account_entry (account_entry_flow_id); create table structure ( id int(10) default 0 not null primary key, name varchar(255) null, info_cell text null, logo longblob null, logo_name varchar(255) null, logo_ext varchar(25) null, logo_size int null, first_hour_displayed time null, last_hour_displayed time null, usual_profiles bigint unsigned default 0 not null, icao varchar(6) null, default_slot_range int unsigned null, min_slot_range tinyint unsigned null, twilight_range tinyint unsigned null, mailing_list_name varchar(255) null, mailing_list_type varchar(255) null, structure_site_url varchar(255) null, default_timezone varchar(255) not null, lang varchar(255) not null, admin_num int unsigned not null, default_view_type int unsigned null, address varchar(255) null, zipcode varchar(255) null, city varchar(255) null, state varchar(255) null, country varchar(255) null, phone varchar(255) null, fax varchar(255) null, email varchar(255) null, default_notification int(3) unsigned null, welcome_cell text null, business text null, default_activity_notification bigint unsigned null, siren int(10) null ); create table supplier_bill ( id int unsigned auto_increment primary key, supplier_bill_type_id int unsigned not null, bill_date datetime null, description varchar(255) null, ordinal int unsigned not null, validated tinyint(1) default 0 null, supplier_bill_file_id int(10) null ); create table supplier_bill_account_entry ( supplier_bill_id int unsigned not null, account_entry_flow_id int unsigned not null, primary key (supplier_bill_id, account_entry_flow_id) ); create index idx_account_entry_flow_id on supplier_bill_account_entry (account_entry_flow_id); create table supplier_bill_email_parsed ( id int(10) auto_increment primary key, uid int(10) null, email_address varchar(255) null, supplier_bill_file_id int(10) null ); create table supplier_bill_file ( id int unsigned auto_increment primary key, file_type varchar(255) null, content longblob null, name varchar(255) not null ); create table supplier_bill_type ( id int unsigned auto_increment primary key, supplier_account_id int unsigned not null, name varchar(255) null, account_id int unsigned not null, vat_account_id int unsigned null, supplier_budget_id int unsigned null, account_budget_id int unsigned null, vat_budget_id int unsigned null, rule text null, sender_email varchar(255) null, subject varchar(255) null ); create table table_trigger ( id int unsigned auto_increment primary key, monitored_table varchar(255) not null, trigger_formula varchar(255) not null ); create table tank ( id int unsigned auto_increment primary key, aircraft_type_id int unsigned not null, tank_type_id int unsigned not null, unit_id int unsigned not null, label varchar(255) not null, max_quantity decimal(15, 2) default -1.00 not null, unlimited_quantity int unsigned default 1 null ); create table track_record ( id int unsigned auto_increment primary key, in_progress tinyint(1) unsigned null, start_date datetime default '0000-00-00 00:00:00' null, activated tinyint(1) unsigned null, datechsys_track_id int unsigned null, resource_cat int unsigned not null, resource_id int unsigned not null, additional_information int unsigned null ) comment 'List of flight record'; create table track_record_data ( id int unsigned auto_increment primary key, record_id int unsigned not null, record_date datetime null, longitude double null, latitude double null, altitude int(7) null, speed double null, track int(3) unsigned null, acc_x int(6) null, acc_y int(6) null, acc_z int(6) null, pressure int(5) unsigned null, battery_level int(4) unsigned null, gps_fix tinyint(1) unsigned null, receive_date datetime null ) comment 'List of flight record data'; create index idx_record_id on track_record_data (record_id); create table track_resource ( id int unsigned auto_increment primary key, name varchar(255) not null ); CREATE TABLE `training` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `training_activity` ( `activity_id` int(10) unsigned NOT NULL, `training_item_id` int(10) unsigned NOT NULL DEFAULT '0', `training_program_id` int(10) unsigned NOT NULL DEFAULT '1', `level_reached` int(10) unsigned DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `validated` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`activity_id`,`training_item_id`,`training_program_id`), KEY `training_program_id_idx` (`training_program_id`), KEY `training_program_item_id_idx` (`training_program_id`,`training_item_id`), CONSTRAINT `training_activity_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `training_activity_training_program_item_id_fk` FOREIGN KEY (`training_program_id`, `training_item_id`) REFERENCES `training_program_item` (`training_program_id`, `training_item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ); CREATE TABLE `training_activity_next_item` ( `student_id` int(10) unsigned NOT NULL, `training_program_id` int(10) unsigned NOT NULL, `training_item_id` int(10) unsigned NOT NULL, `reporting_offset` tinyint(10) unsigned NOT NULL DEFAULT '0', `item_preselected` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`student_id`,`training_program_id`,`training_item_id`), KEY `training_item_id_idx` (`training_item_id`), KEY `training_program_id_idx` (`training_program_id`), KEY `training_activity_next_item_training_program_item_id_fk` (`training_program_id`,`training_item_id`), CONSTRAINT `training_activity_next_item_student_id_fk` FOREIGN KEY (`student_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `training_activity_next_item_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `training_activity_next_item_training_program_item_id_fk` FOREIGN KEY (`training_program_id`, `training_item_id`) REFERENCES `training_program_item` (`training_program_id`, `training_item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ); CREATE TABLE `training_activity_person` ( `activity_id` int(10) unsigned NOT NULL, `person_id` int(10) unsigned NOT NULL, `num` int(10) unsigned NOT NULL DEFAULT '0', `training_checking_date` datetime DEFAULT NULL, `training_checking_sentence` varchar(255) DEFAULT NULL, PRIMARY KEY (`activity_id`,`person_id`,`num`), KEY `person_id_idx` (`person_id`), CONSTRAINT `training_activity_person_activity_id_fk` FOREIGN KEY (`activity_id`) REFERENCES `flight` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `training_activity_person_person_id_fk` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ); CREATE TABLE `training_phase` ( `training_program_item_order_num` int(11) unsigned NOT NULL, `training_program_id` int(10) unsigned NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`training_program_item_order_num`,`training_program_id`), KEY `training_program_id_idx` (`training_program_id`), CONSTRAINT `training_phase_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ); CREATE TABLE `training_program` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `training_id` int(10) unsigned DEFAULT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`), KEY `training_id_idx` (`training_id`), CONSTRAINT `training_program_training_id_fk` FOREIGN KEY (`training_id`) REFERENCES `training` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ); CREATE TABLE `training_program_activity` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `training_program_id` int(10) unsigned NOT NULL, `activity_type_id` bigint(20) unsigned NOT NULL, `duration` int(11) unsigned DEFAULT NULL, `breaktime` int(11) unsigned DEFAULT NULL, `order_num` int(11) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `training_program_id_idx` (`training_program_id`), KEY `activity_type_id_idx` (`activity_type_id`), CONSTRAINT `training_program_activity_activity_type_id_fk` FOREIGN KEY (`activity_type_id`) REFERENCES `activity_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `training_program_activity_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ); CREATE TABLE `training_item` ( `training_id` int(10) unsigned NOT NULL, `training_item_label_id` int(10) unsigned NOT NULL, `number_of_sessions` int(11) NOT NULL, `order_num` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`training_id`,`training_item_label_id`), KEY `order_num_idx` (`order_num` ASC), CONSTRAINT `training_item_training_id_fk` FOREIGN KEY (`training_id`) REFERENCES `training` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ); create table uncomp_flight_type ( id1 int unsigned null, id2 int unsigned null ) comment 'List of sales'; create table validity_type ( id int unsigned auto_increment primary key, name varchar(255) not null, time_limitation tinyint(1) default 0 not null, ident_value_enable tinyint(1) default 0 not null, grant_date_enable tinyint(1) default 0 not null, mandatory tinyint(1) default 0 not null, experience_formula text null, mandatory_access_control tinyint(1) unsigned default 0 null, alert_on_login int(10) default -2 null, first_reminder_alert int unsigned default 0 null, reminder_frequency_alert int unsigned default 0 null, associate_attachment tinyint(1) unsigned default 0 not null, certification_process tinyint(1) default 0 not null, is_contract tinyint(1) default 0 not null, contract_filename varchar(255) not null, contract_file mediumblob not null, contract_file_extension varchar(4) not null, is_OF_contract tinyint(1) default 0 not null, activated tinyint(1) unsigned default 1 not null ); create table validity ( id int unsigned auto_increment primary key, person_id int unsigned not null, validity_type_id int unsigned not null, registration_date datetime not null, expire_date date null, no_alert tinyint(1) default 0 not null, ident_value varchar(255) null, grant_date date null, checker_person_id int unsigned not null, checking_date date not null, checking_sentence varchar(255) not null, is_current_validity tinyint(1) default 0 not null, constraint validity_person_id_validity_type_id_registration_date_uindex unique (person_id, validity_type_id, registration_date), constraint validity_person_id_fk foreign key (person_id) references person (id), constraint validity_validity_type_id_fk foreign key (validity_type_id) references validity_type (id) ); create table validity_type_page ( id int unsigned auto_increment primary key, validity_type_id int unsigned not null, page_index int unsigned not null, label varchar(40) not null, is_mandatory tinyint(1) unsigned not null, constraint validity_type_page_validity_type_id_page_index_uindex unique (validity_type_id, page_index), constraint validity_type_page_validity_type_id_fk foreign key (validity_type_id) references validity_type (id) ); create table validity_page ( id int unsigned auto_increment primary key, validity_type_page_id int unsigned not null, filename varchar(255) not null, file mediumblob not null, file_extension varchar(4) not null, constraint validity_page_validity_type_page_id_fk foreign key (validity_type_page_id) references validity_type_page (id) ); create table validity_2_validity_page ( validity_id int unsigned not null, validity_page_id int unsigned not null, primary key (validity_id, validity_page_id), constraint validity_2_validity_page_validity_id_fk foreign key (validity_id) references validity (id), constraint validity_2_validity_page_validity_page_id_fk foreign key (validity_page_id) references validity_page (id) ); create table variable ( id int auto_increment primary key, variable varchar(255) not null, label varchar(255) not null, category tinyint(1) null, value_type varchar(255) null, order_num int not null ); create table variable_value ( id int auto_increment primary key, variable_id int not null, assign_value decimal(15, 2) not null, start_date datetime not null ); create table web_feed ( id int unsigned auto_increment primary key, channel_id int unsigned not null, publication_date datetime not null, title varchar(255) null, content text null ); create table web_feed_channel ( id int unsigned auto_increment primary key, label varchar(255) not null ); create definer = overallCustomer@localhost view eligible_pilot_for_first_flight as select `of40_aeroclublys`.`person`.`id` AS `person_id` from `of40_aeroclublys`.`person` where ((`of40_aeroclublys`.`person`.`activated` = 1) and ((`of40_aeroclublys`.`person`.`profile` & (select `of40_aeroclublys`.`parameter`.`int_value` from `of40_aeroclublys`.`parameter` where (`of40_aeroclublys`.`parameter`.`code` = 'EXTERNAL_BOOKING_PILOT_PROFILE'))) > 0) and `of40_aeroclublys`.`person`.`id` in (select `of40_aeroclublys`.`regular_presence_inst_date`.`person_id` from `of40_aeroclublys`.`regular_presence_inst_date` union select `of40_aeroclublys`.`exceptionnal_inst_date`.`person_id` from `of40_aeroclublys`.`exceptionnal_inst_date` where ((`of40_aeroclublys`.`exceptionnal_inst_date`.`end_date` > now()) and (`of40_aeroclublys`.`exceptionnal_inst_date`.`presence` = 1)))); create definer = overallCustomer@localhost view eligible_resource_for_first_flight as select `of40_aeroclublys`.`resource`.`id` AS `resource_id` from ((`of40_aeroclublys`.`resource` join `of40_aeroclublys`.`business_field_content` on (( `of40_aeroclublys`.`business_field_content`.`category_id` = `of40_aeroclublys`.`resource`.`id`))) join `of40_aeroclublys`.`business_field` on ((`of40_aeroclublys`.`business_field`.`id` = `of40_aeroclublys`.`business_field_content`.`business_field_id`))) where ((`of40_aeroclublys`.`resource`.`activated` = 1) and (`of40_aeroclublys`.`business_field`.`variable` = 'canResourcePerformFirstFlights') and (`of40_aeroclublys`.`business_field_content`.`content` = '1') and (`of40_aeroclublys`.`resource`.`bookable` = 1) and ((select count(0) from `of40_aeroclublys`.`resource_type_place_tag` `seat` where (`seat`.`resource_type_id` = `of40_aeroclublys`.`resource`.`resource_type_id`)) >= 2) and `of40_aeroclublys`.`resource`.`id` in (select `of40_aeroclublys`.`resource_regular_availability`.`resource_id` from `of40_aeroclublys`.`resource_regular_availability` union select `of40_aeroclublys`.`resource_exceptional_availability`.`resource_id` from `of40_aeroclublys`.`resource_exceptional_availability` where ((`of40_aeroclublys`.`resource_exceptional_availability`.`end_date` > now()) and (`of40_aeroclublys`.`resource_exceptional_availability`.`presence` = 1)))); create definer = root@localhost function distanceBetween2Point(latitude1 double, longitude1 double, latitude2 double, longitude2 double) returns double BEGIN DECLARE rlongitude1 DOUBLE; DECLARE rlatitude1 DOUBLE; DECLARE rlongitude2 DOUBLE; DECLARE rlatitude2 DOUBLE; DECLARE dlongitude DOUBLE; DECLARE dlatitude DOUBLE; DECLARE a DOUBLE; SET rlongitude1 = RADIANS(longitude1); SET rlatitude1 = RADIANS(latitude1); SET rlongitude2 = RADIANS(longitude2); SET rlatitude2 = RADIANS(latitude2); SET dlongitude = (rlongitude2 - rlongitude1) / 2; SET dlatitude = (rlatitude2 - rlatitude1) / 2; SET a = SIN(dlatitude) * SIN(dlatitude) + COS(rlatitude1) * COS(rlatitude2) * SIN(dlongitude) * SIN(dlongitude); RETURN (6378137 * 2 * ATAN2(SQRT(a), SQRT(1 - a))); END; create definer = overallCustomer@localhost function formatDecimal(string varchar(255)) returns varchar(255) BEGIN DECLARE replacedString VARCHAR(255); SET replacedString = string; SET @decimalSeparator = (SELECT char_value FROM parameter WHERE code = 'DECIMAL_SEPARATOR' AND key_id = 0 LIMIT 1); SELECT IF(replacedString <> '0.00' AND replacedString <> '0', TRIM(TRAILING '0' FROM replacedString), replacedString) INTO replacedString; SELECT TRIM(TRAILING '.' FROM replacedString) INTO replacedString; SELECT REPLACE(replacedString, '.', IFNULL(@decimalSeparator, '.')) INTO replacedString; RETURN replacedString; END; create definer = root@localhost function isBalancedAccountEntryFlow(flowId int) returns int BEGIN DECLARE isBalanced INT; SELECT IF(SUM(debit) <> SUM(credit), 0, 1) into isBalanced FROM account_entry WHERE flow_id = flowId GROUP BY flow_id; RETURN isBalanced; END; create definer = root@localhost function nearestPoint(latRef double, longRef double, distanceMax int) returns varchar(6) BEGIN DECLARE rlongitude1 DOUBLE; DECLARE rlatitude1 DOUBLE; DECLARE rlongitude2 DOUBLE; DECLARE rlatitude2 DOUBLE; DECLARE a DOUBLE; DECLARE icao_name VARCHAR(6); DECLARE lenght DOUBLE; SET a = 2 * 6378137 * pi() * distanceMax / (1852 * 60 * 360); SET rlongitude1 = longRef + a / cos(latRef); SET rlongitude2 = longRef - a / cos(latRef); SET rlatitude1 = latRef + a; SET rlatitude2 = latRef - a; SELECT loc1.icao_name, distanceBetween2Point( (SELECT loc2.latitude FROM location AS loc2 WHERE loc2.icao_name = loc1.icao_name), (SELECT loc3.longitude FROM location AS loc3 WHERE loc3.icao_name = loc1.icao_name), latRef, longRef) AS distance FROM location AS loc1 WHERE IF(rlongitude2 < rlongitude1, loc1.longitude BETWEEN rlongitude2 AND rlongitude1, loc1.longitude BETWEEN rlongitude1 AND rlongitude2) AND IF(rlatitude2 < rlatitude1, loc1.latitude BETWEEN rlatitude2 AND rlatitude1, loc1.latitude BETWEEN rlatitude1 AND rlatitude2) HAVING distance < distanceMax ORDER BY distance LIMIT 1 INTO icao_name, lenght; RETURN icao_name; END; create definer = overallCustomer@localhost function sexa2HoursHundredths(sexacentimal int) returns varchar(255) BEGIN DECLARE convertedValue VARCHAR(255); DECLARE hours VARCHAR(255); DECLARE roundedHours VARCHAR(255); DECLARE remainingTime VARCHAR(255); DECLARE decimalSeparator VARCHAR(1); SET hours = sexacentimal / 600; SET roundedHours = IF(sexacentimal > 0, FLOOR(hours), CEIL(hours)); SET remainingTime = ABS(sexacentimal - roundedHours * 600); SET decimalSeparator = (SELECT IFNULL(char_value, '.') FROM parameter WHERE code = 'DECIMAL_SEPARATOR' AND key_id = 0 LIMIT 1); SELECT CONCAT( roundedHours, decimalSeparator, LPAD(FLOOR(remainingTime / 6), 2, '0') ) INTO convertedValue; RETURN convertedValue; END; create definer = overallCustomer@localhost function sexa2HoursMinute(sexacentimal int) returns varchar(255) BEGIN DECLARE convertedValue VARCHAR(255); DECLARE hours VARCHAR(255); # We separate hours computation from roundHours in order to keep the minus sign if hours is 0 but sexacentimal negative DECLARE roundedHours VARCHAR(255); DECLARE remainingTime VARCHAR(255); SET hours = sexacentimal / 600; SET roundedHours = IF(sexacentimal > 0, FLOOR(hours), CEIL(hours)); SET remainingTime = ABS(sexacentimal - roundedHours * 600); SELECT CONCAT( roundedHours, ':', LPAD(FLOOR(remainingTime / 10), 2, '0') ) INTO convertedValue; RETURN convertedValue; END; create definer = root@localhost function stripChars(word varchar(255)) returns varchar(255) BEGIN DECLARE stripWord VARCHAR(255); SET stripWord = word; SELECT IF(stripWord REGEXP '[-]', REPLACE(stripWord, '-', ''), stripWord) INTO stripWord; SELECT IF(stripWord REGEXP '[ ]', REPLACE(stripWord, ' ', ''), stripWord) INTO stripWord; RETURN stripWord; END; create definer = overallCustomer@localhost function sumAccountEntry(accountId int, endDate datetime) returns decimal(15, 2) BEGIN DECLARE balanceDate DATETIME; DECLARE balanceDebit DECIMAL(15, 2); DECLARE balanceCredit DECIMAL(15, 2); DECLARE totalDebit DECIMAL(15, 2); DECLARE totalCredit DECIMAL(15, 2); DECLARE totalBalance DECIMAL(15, 2); SELECT balance_date, IFNULL(debit, 0.00), IFNULL(credit, 0.00) INTO balanceDate, balanceDebit, balanceCredit FROM balance_date LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId) WHERE balance_date.balance_date < endDate ORDER BY balance_date DESC LIMIT 1; IF (SELECT FOUND_ROWS()) = 0 THEN SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit, balanceCredit; END IF; SELECT IFNULL(SUM(debit), 0.00) + balanceDebit, IFNULL(SUM(credit), 0.00) + balanceCredit INTO totalDebit, totalCredit FROM account_entry WHERE account_id = accountId AND account_date >= CAST(balanceDate AS DATETIME) AND account_date < CAST(endDate AS DATETIME); SELECT (totalCredit - totalDebit) INTO totalBalance; RETURN totalBalance; END; create definer = overallCustomer@localhost function sumAccountEntryCredit(accountId int, endDate datetime) returns decimal(15, 2) BEGIN DECLARE balanceDate DATETIME; DECLARE balanceCredit DECIMAL(15, 2); DECLARE totalCredit DECIMAL(15, 2); SELECT balance_date, IFNULL(credit, 0.00) INTO balanceDate, balanceCredit FROM balance_date LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId) WHERE balance_date.balance_date < endDate ORDER BY balance_date DESC LIMIT 1; IF (SELECT FOUND_ROWS()) = 0 THEN SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceCredit; END IF; SELECT IFNULL(SUM(credit), 0.00) + balanceCredit INTO totalCredit FROM account_entry WHERE account_id = accountId AND account_date >= CAST(balanceDate AS DATETIME) AND account_date < CAST(endDate AS DATETIME); RETURN totalCredit; END; create definer = overallCustomer@localhost function sumAccountEntryDebit(accountId int, endDate datetime) returns decimal(15, 2) BEGIN DECLARE balanceDate DATETIME; DECLARE balanceDebit DECIMAL(15, 2); DECLARE totalDebit DECIMAL(15, 2); SELECT balance_date, IFNULL(debit, 0.00) INTO balanceDate, balanceDebit FROM balance_date LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId) WHERE balance_date.balance_date < endDate ORDER BY balance_date DESC LIMIT 1; IF (SELECT FOUND_ROWS()) = 0 THEN SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit; END IF; SELECT IFNULL(SUM(debit), 0.00) + balanceDebit INTO totalDebit FROM account_entry WHERE account_id = accountId AND account_date >= CAST(balanceDate AS DATETIME) AND account_date < CAST(endDate AS DATETIME); RETURN totalDebit; END; create definer = root@localhost function sumValidatedAccountEntry(accountId int, endDate datetime) returns decimal(15, 2) BEGIN DECLARE balanceDate DATETIME; DECLARE balanceDebit DECIMAL(15, 2); DECLARE balanceCredit DECIMAL(15, 2); DECLARE totalDebit DECIMAL(15, 2); DECLARE totalCredit DECIMAL(15, 2); DECLARE totalBalance DECIMAL(15, 2); SELECT balance_date, IFNULL(debit, 0.00), IFNULL(credit, 0.00) INTO balanceDate, balanceDebit, balanceCredit FROM balance_date LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId) WHERE balance_date.balance_date < endDate ORDER BY balance_date DESC LIMIT 1; IF (SELECT FOUND_ROWS()) = 0 THEN SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit, balanceCredit; END IF; SELECT IFNULL(SUM(debit), 0.00) + balanceDebit, IFNULL(SUM(credit), 0.00) + balanceCredit INTO totalDebit, totalCredit FROM account_entry WHERE account_id = accountId AND account_date >= CAST(balanceDate AS DATETIME) AND account_date < CAST(endDate AS DATETIME) AND validated = 1; SELECT (totalCredit - totalDebit) INTO totalBalance; RETURN totalBalance; END;
Description
Table booking
Le contenu du champ aircraft_id de la table booking peut être nul. Dans ce cas, c'est une ancienne réservation effectuée sur une ressource non-existante ou qui n'existe plus; aussi nommé réservation orpheline.
Table flight
- airborne :
- 0: Pas en l'air ou fermeture de vol : Le pilote a terminé le vol.
- 1: En l'air ou ouverture de vol : Le pilote remplit le vol avant de voler.
Champs cachés
Les champs suivants ne peuvent être exportés et ne doivent pas être inclus dans une requête SELECT. De plus, un "SELECT *" est interdit quand une des tables contient un champ caché :
- Table journal : Tous les champs
- Table log : Tous les champs
- Table parameter : Tous les champs
- Table person :
- hash_password
- Table structure :
- address
- admin_num
- city
- country
- default_notification
- default_slot_range
- default_timezone
- default_view_type
- fax
- first_hour_displayed
- info_cell
- lang
- last_hour_displayed
- logo
- logo_name
- logo_ext
- logo_size
- mail_from_address
- mailing_list_name
- mailing_list_type
- min_slot_range
- name
- phone
- state
- twilight_range
- usual_profiles
- welcome_cell
- zipcode
- Table oauth_client : Tous les champs
- Table oauth_access_token : Tous les champs
- Table oauth_auth_code : Tous les champs
- Table oauth_refresh_token : Tous les champs
Exemple de requêtes non autorisées :
SELECT * FROM structure; SELECT hash_password FROM person; SELECT person.id, validity.* FROM person LEFT JOIN validity ON (person.id=validity.person_id);