Structure de la base de données
Jump to navigation
Jump to search
Schéma de la structure de la base de données
Structure
<sql>create table account (
id int unsigned auto_increment
primary key,
creation_date date null,
name text null,
export_account varchar(255) null,
activated tinyint(1) unsigned default 1 not null,
category tinyint(1) unsigned default 0 null,
account_type tinyint(1) unsigned default 0 null,
owner_id int unsigned default 0 not null,
payment_allowed tinyint(1) default 0 null,
budget_id int unsigned null,
order_num int null,
accounting_id int unsigned null,
deactivated_date datetime null,
group_sales tinyint(1) default 0 not null
)
comment 'List of account';
create index idx_owner_id
on account (owner_id);
create table account_entry (
id int 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_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(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 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 `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, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`)
);
CREATE TABLE `training_program_item` (
`training_program_id` int(10) unsigned NOT NULL, `training_item_id` int(10) unsigned NOT NULL, `number_of_sessions` int(11) NOT NULL, `order_num` int(11) NOT NULL, PRIMARY KEY (`training_program_id`,`training_item_id`), CONSTRAINT `training_program_item_training_program_id_fk` FOREIGN KEY (`training_program_id`) REFERENCES `training_program` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);
create table uncomp_flight_type (
id1 int unsigned null, id2 int unsigned null
)
comment 'List of sales';
create table validity_type (
id int unsigned auto_increment
primary key,
name varchar(255) not null,
time_limitation tinyint(1) default 0 not null,
ident_value_enable tinyint(1) default 0 not null,
grant_date_enable tinyint(1) default 0 not null,
mandatory tinyint(1) default 0 not null,
experience_formula text null,
mandatory_access_control tinyint(1) unsigned default 0 null,
alert_on_login int(10) default -2 null,
first_reminder_alert int unsigned default 0 null,
reminder_frequency_alert int unsigned default 0 null,
associate_attachment tinyint(1) unsigned default 0 not null,
certification_process tinyint(1) default 0 not null,
is_contract tinyint(1) default 0 not null,
contract_filename varchar(255) not null,
contract_file mediumblob not null,
contract_file_extension varchar(4) not null,
is_OF_contract tinyint(1) default 0 not null,
activated tinyint(1) unsigned default 1 not null
);
create table validity (
id int unsigned auto_increment
primary key,
person_id int unsigned not null,
validity_type_id int unsigned not null,
registration_date datetime not null,
expire_date date null,
no_alert tinyint(1) default 0 not null,
ident_value varchar(255) null,
grant_date date null,
checker_person_id int unsigned not null,
checking_date date not null,
checking_sentence varchar(255) not null,
is_current_validity tinyint(1) default 0 not null,
constraint validity_person_id_validity_type_id_registration_date_uindex
unique (person_id, validity_type_id, registration_date),
constraint validity_person_id_fk
foreign key (person_id) references person (id),
constraint validity_validity_type_id_fk
foreign key (validity_type_id) references validity_type (id)
);
create table validity_type_page (
id int unsigned auto_increment
primary key,
validity_type_id int unsigned not null,
page_index int unsigned not null,
label varchar(40) not null,
is_mandatory tinyint(1) unsigned not null,
constraint validity_type_page_validity_type_id_page_index_uindex
unique (validity_type_id, page_index),
constraint validity_type_page_validity_type_id_fk
foreign key (validity_type_id) references validity_type (id)
);
create table validity_page (
id int unsigned auto_increment
primary key,
validity_type_page_id int unsigned not null,
filename varchar(255) not null,
file mediumblob not null,
file_extension varchar(4) not null,
constraint validity_page_validity_type_page_id_fk
foreign key (validity_type_page_id) references validity_type_page (id)
);
create table validity_2_validity_page (
validity_id int unsigned not null,
validity_page_id int unsigned not null,
primary key (validity_id, validity_page_id),
constraint validity_2_validity_page_validity_id_fk
foreign key (validity_id) references validity (id),
constraint validity_2_validity_page_validity_page_id_fk
foreign key (validity_page_id) references validity_page (id)
);
create table variable (
id int auto_increment
primary key,
variable varchar(255) not null,
label varchar(255) not null,
category tinyint(1) null,
value_type varchar(255) null,
order_num int not null
);
create table variable_value (
id int auto_increment
primary key,
variable_id int not null,
assign_value decimal(15, 2) not null,
start_date datetime not null
);
create table web_feed (
id int unsigned auto_increment
primary key,
channel_id int unsigned not null,
publication_date datetime not null,
title varchar(255) null,
content text null
);
create table web_feed_channel (
id int unsigned auto_increment
primary key,
label varchar(255) not null
);
create definer = overallCustomer@localhost view eligible_pilot_for_first_flight as select `of40_aeroclublys`.`person`.`id` AS `person_id` from `of40_aeroclublys`.`person` where ((`of40_aeroclublys`.`person`.`activated` = 1) and
((`of40_aeroclublys`.`person`.`profile` & (select `of40_aeroclublys`.`parameter`.`int_value`
from `of40_aeroclublys`.`parameter`
where (`of40_aeroclublys`.`parameter`.`code` =
'EXTERNAL_BOOKING_PILOT_PROFILE'))) > 0) and
`of40_aeroclublys`.`person`.`id` in (select `of40_aeroclublys`.`regular_presence_inst_date`.`person_id`
from `of40_aeroclublys`.`regular_presence_inst_date`
union
select `of40_aeroclublys`.`exceptionnal_inst_date`.`person_id`
from `of40_aeroclublys`.`exceptionnal_inst_date`
where ((`of40_aeroclublys`.`exceptionnal_inst_date`.`end_date` > now()) and
(`of40_aeroclublys`.`exceptionnal_inst_date`.`presence` = 1))));
create definer = overallCustomer@localhost view eligible_resource_for_first_flight as select `of40_aeroclublys`.`resource`.`id` AS `resource_id` from ((`of40_aeroclublys`.`resource` join `of40_aeroclublys`.`business_field_content` on ((
`of40_aeroclublys`.`business_field_content`.`category_id` = `of40_aeroclublys`.`resource`.`id`)))
join `of40_aeroclublys`.`business_field` on ((`of40_aeroclublys`.`business_field`.`id` =
`of40_aeroclublys`.`business_field_content`.`business_field_id`)))
where ((`of40_aeroclublys`.`resource`.`activated` = 1) and
(`of40_aeroclublys`.`business_field`.`variable` = 'canResourcePerformFirstFlights') and
(`of40_aeroclublys`.`business_field_content`.`content` = '1') and
(`of40_aeroclublys`.`resource`.`bookable` = 1) and ((select count(0)
from `of40_aeroclublys`.`resource_type_place_tag` `seat`
where (`seat`.`resource_type_id` =
`of40_aeroclublys`.`resource`.`resource_type_id`)) >=
2) and
`of40_aeroclublys`.`resource`.`id` in (select `of40_aeroclublys`.`resource_regular_availability`.`resource_id`
from `of40_aeroclublys`.`resource_regular_availability`
union
select `of40_aeroclublys`.`resource_exceptional_availability`.`resource_id`
from `of40_aeroclublys`.`resource_exceptional_availability`
where ((`of40_aeroclublys`.`resource_exceptional_availability`.`end_date` >
now()) and
(`of40_aeroclublys`.`resource_exceptional_availability`.`presence` = 1))));
create
definer = root@localhost function distanceBetween2Point(latitude1 double, longitude1 double, latitude2 double,
longitude2 double) returns double
BEGIN
DECLARE rlongitude1 DOUBLE; DECLARE rlatitude1 DOUBLE; DECLARE rlongitude2 DOUBLE; DECLARE rlatitude2 DOUBLE; DECLARE dlongitude DOUBLE; DECLARE dlatitude DOUBLE; DECLARE a DOUBLE;
SET rlongitude1 = RADIANS(longitude1); SET rlatitude1 = RADIANS(latitude1); SET rlongitude2 = RADIANS(longitude2); SET rlatitude2 = RADIANS(latitude2); SET dlongitude = (rlongitude2 - rlongitude1) / 2; SET dlatitude = (rlatitude2 - rlatitude1) / 2; SET a = SIN(dlatitude) * SIN(dlatitude) + COS(rlatitude1) * COS(rlatitude2) * SIN(dlongitude) * SIN(dlongitude); RETURN (6378137 * 2 * ATAN2(SQRT(a), SQRT(1 - a)));
END;
create
definer = overallCustomer@localhost function formatDecimal(string varchar(255)) returns varchar(255)
BEGIN
DECLARE replacedString VARCHAR(255); SET replacedString = string; SET @decimalSeparator = (SELECT char_value FROM parameter WHERE code = 'DECIMAL_SEPARATOR' AND key_id = 0 LIMIT 1);
SELECT IF(replacedString <> '0.00' AND replacedString <> '0', TRIM(TRAILING '0' FROM replacedString),
replacedString)
INTO replacedString;
SELECT TRIM(TRAILING '.' FROM replacedString) INTO replacedString;
SELECT REPLACE(replacedString, '.', IFNULL(@decimalSeparator, '.')) INTO replacedString;
RETURN replacedString;
END;
create
definer = root@localhost function isBalancedAccountEntryFlow(flowId int) returns int
BEGIN
DECLARE isBalanced INT;
SELECT IF(SUM(debit) <> SUM(credit), 0, 1) into isBalanced FROM account_entry WHERE flow_id = flowId GROUP BY flow_id;
RETURN isBalanced;
END;
create
definer = root@localhost function nearestPoint(latRef double, longRef double, distanceMax int) returns varchar(6)
BEGIN
DECLARE rlongitude1 DOUBLE; DECLARE rlatitude1 DOUBLE; DECLARE rlongitude2 DOUBLE; DECLARE rlatitude2 DOUBLE; DECLARE a DOUBLE; DECLARE icao_name VARCHAR(6); DECLARE lenght DOUBLE;
SET a = 2 * 6378137 * pi() * distanceMax / (1852 * 60 * 360); SET rlongitude1 = longRef + a / cos(latRef); SET rlongitude2 = longRef - a / cos(latRef); SET rlatitude1 = latRef + a; SET rlatitude2 = latRef - a;
SELECT loc1.icao_name,
distanceBetween2Point(
(SELECT loc2.latitude FROM location AS loc2 WHERE loc2.icao_name = loc1.icao_name),
(SELECT loc3.longitude FROM location AS loc3 WHERE loc3.icao_name = loc1.icao_name),
latRef,
longRef) AS distance
FROM location AS loc1
WHERE IF(rlongitude2 < rlongitude1,
loc1.longitude BETWEEN rlongitude2 AND rlongitude1,
loc1.longitude BETWEEN rlongitude1 AND rlongitude2)
AND IF(rlatitude2 < rlatitude1,
loc1.latitude BETWEEN rlatitude2 AND rlatitude1,
loc1.latitude BETWEEN rlatitude1 AND rlatitude2)
HAVING distance < distanceMax
ORDER BY distance
LIMIT 1
INTO icao_name, lenght;
RETURN icao_name;
END;
create
definer = overallCustomer@localhost function sexa2HoursHundredths(sexacentimal int) returns varchar(255)
BEGIN
DECLARE convertedValue VARCHAR(255);
DECLARE hours VARCHAR(255);
DECLARE roundedHours VARCHAR(255);
DECLARE remainingTime VARCHAR(255);
DECLARE decimalSeparator VARCHAR(1);
SET hours = sexacentimal / 600;
SET roundedHours = IF(sexacentimal > 0, FLOOR(hours), CEIL(hours));
SET remainingTime = ABS(sexacentimal - roundedHours * 600);
SET decimalSeparator =
(SELECT IFNULL(char_value, '.') FROM parameter WHERE code = 'DECIMAL_SEPARATOR' AND key_id = 0 LIMIT 1);
SELECT CONCAT(
roundedHours, decimalSeparator, LPAD(FLOOR(remainingTime / 6), 2, '0')
)
INTO convertedValue;
RETURN convertedValue;
END;
create
definer = overallCustomer@localhost function sexa2HoursMinute(sexacentimal int) returns varchar(255)
BEGIN
DECLARE convertedValue VARCHAR(255); DECLARE hours VARCHAR(255); # We separate hours computation from roundHours in order to keep the minus sign if hours is 0 but sexacentimal negative DECLARE roundedHours VARCHAR(255); DECLARE remainingTime VARCHAR(255); SET hours = sexacentimal / 600; SET roundedHours = IF(sexacentimal > 0, FLOOR(hours), CEIL(hours)); SET remainingTime = ABS(sexacentimal - roundedHours * 600);
SELECT CONCAT(
roundedHours, ':', LPAD(FLOOR(remainingTime / 10), 2, '0')
)
INTO convertedValue;
RETURN convertedValue;
END;
create
definer = root@localhost function stripChars(word varchar(255)) returns varchar(255)
BEGIN
DECLARE stripWord VARCHAR(255); SET stripWord = word; SELECT IF(stripWord REGEXP '[-]', REPLACE(stripWord, '-', ), stripWord) INTO stripWord; SELECT IF(stripWord REGEXP '[ ]', REPLACE(stripWord, ' ', ), stripWord) INTO stripWord; RETURN stripWord;
END;
create
definer = overallCustomer@localhost function sumAccountEntry(accountId int, endDate datetime) returns decimal(15, 2)
BEGIN
DECLARE balanceDate DATETIME; DECLARE balanceDebit DECIMAL(15, 2); DECLARE balanceCredit DECIMAL(15, 2); DECLARE totalDebit DECIMAL(15, 2); DECLARE totalCredit DECIMAL(15, 2); DECLARE totalBalance DECIMAL(15, 2);
SELECT balance_date, IFNULL(debit, 0.00), IFNULL(credit, 0.00)
INTO balanceDate, balanceDebit, balanceCredit
FROM balance_date
LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId)
WHERE balance_date.balance_date < endDate
ORDER BY balance_date DESC
LIMIT 1;
IF (SELECT FOUND_ROWS()) = 0 THEN
SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit, balanceCredit;
END IF;
SELECT IFNULL(SUM(debit), 0.00) + balanceDebit, IFNULL(SUM(credit), 0.00) + balanceCredit
INTO totalDebit, totalCredit
FROM account_entry
WHERE account_id = accountId
AND account_date >= CAST(balanceDate AS DATETIME)
AND account_date < CAST(endDate AS DATETIME);
SELECT (totalCredit - totalDebit) INTO totalBalance;
RETURN totalBalance;
END;
create
definer = overallCustomer@localhost function sumAccountEntryCredit(accountId int, endDate datetime) returns decimal(15, 2)
BEGIN
DECLARE balanceDate DATETIME; DECLARE balanceCredit DECIMAL(15, 2); DECLARE totalCredit DECIMAL(15, 2);
SELECT balance_date, IFNULL(credit, 0.00)
INTO balanceDate, balanceCredit
FROM balance_date
LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId)
WHERE balance_date.balance_date < endDate
ORDER BY balance_date DESC
LIMIT 1;
IF (SELECT FOUND_ROWS()) = 0 THEN
SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceCredit;
END IF;
SELECT IFNULL(SUM(credit), 0.00) + balanceCredit
INTO totalCredit
FROM account_entry
WHERE account_id = accountId
AND account_date >= CAST(balanceDate AS DATETIME)
AND account_date < CAST(endDate AS DATETIME);
RETURN totalCredit;
END;
create
definer = overallCustomer@localhost function sumAccountEntryDebit(accountId int, endDate datetime) returns decimal(15, 2)
BEGIN
DECLARE balanceDate DATETIME; DECLARE balanceDebit DECIMAL(15, 2); DECLARE totalDebit DECIMAL(15, 2);
SELECT balance_date, IFNULL(debit, 0.00)
INTO balanceDate, balanceDebit
FROM balance_date
LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId)
WHERE balance_date.balance_date < endDate
ORDER BY balance_date DESC
LIMIT 1;
IF (SELECT FOUND_ROWS()) = 0 THEN
SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit;
END IF;
SELECT IFNULL(SUM(debit), 0.00) + balanceDebit
INTO totalDebit
FROM account_entry
WHERE account_id = accountId
AND account_date >= CAST(balanceDate AS DATETIME)
AND account_date < CAST(endDate AS DATETIME);
RETURN totalDebit;
END;
create
definer = root@localhost function sumValidatedAccountEntry(accountId int, endDate datetime) returns decimal(15, 2)
BEGIN
DECLARE balanceDate DATETIME; DECLARE balanceDebit DECIMAL(15, 2); DECLARE balanceCredit DECIMAL(15, 2); DECLARE totalDebit DECIMAL(15, 2); DECLARE totalCredit DECIMAL(15, 2); DECLARE totalBalance DECIMAL(15, 2);
SELECT balance_date, IFNULL(debit, 0.00), IFNULL(credit, 0.00)
INTO balanceDate, balanceDebit, balanceCredit
FROM balance_date
LEFT JOIN balance ON (balance_date.id = balance.balance_date_id AND balance.account_id = accountId)
WHERE balance_date.balance_date < endDate
ORDER BY balance_date DESC
LIMIT 1;
IF (SELECT FOUND_ROWS()) = 0 THEN
SELECT '0000-00-00 00:00:00', 0.00, 0.00 INTO balanceDate, balanceDebit, balanceCredit;
END IF;
SELECT IFNULL(SUM(debit), 0.00) + balanceDebit, IFNULL(SUM(credit), 0.00) + balanceCredit
INTO totalDebit, totalCredit
FROM account_entry
WHERE account_id = accountId
AND account_date >= CAST(balanceDate AS DATETIME)
AND account_date < CAST(endDate AS DATETIME)
AND validated = 1;
SELECT (totalCredit - totalDebit) INTO totalBalance;
RETURN totalBalance;
END;</sql>
Description
Table booking
Le contenu du champ aircraft_id de la table booking peut être nul. Dans ce cas, c'est une ancienne réservation effectuée sur une ressource non-existante ou qui n'existe plus; aussi nommé réservation orpheline.
Table flight
- airborne :
- 0: Pas en l'air ou fermeture de vol : Le pilote a terminé le vol.
- 1: En l'air ou ouverture de vol : Le pilote remplit le vol avant de voler.
Champs cachés
Les champs suivants ne peuvent être exportés et ne doivent pas être inclus dans une requête SELECT. De plus, un "SELECT *" est interdit quand une des tables contient un champ caché :
- Table journal : Tous les champs
- Table log : Tous les champs
- Table parameter : Tous les champs
- Table person :
- hash_password
- Table structure :
- address
- admin_num
- city
- country
- default_notification
- default_slot_range
- default_timezone
- default_view_type
- fax
- first_hour_displayed
- info_cell
- lang
- last_hour_displayed
- logo
- logo_name
- logo_ext
- logo_size
- mail_from_address
- mailing_list_name
- mailing_list_type
- min_slot_range
- name
- phone
- state
- twilight_range
- usual_profiles
- welcome_cell
- zipcode
- Table oauth_client : Tous les champs
- Table oauth_access_token : Tous les champs
- Table oauth_auth_code : Tous les champs
- Table oauth_refresh_token : Tous les champs
Exemple de requêtes non autorisées : <sql>SELECT * FROM structure;
SELECT hash_password FROM person;
SELECT person.id, validity.* FROM person LEFT JOIN validity ON (person.id=validity.person_id);</sql>