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
create table account
(
id int unsigned auto_increment
primary key,
creation_date date null,
name text null,
export_account varchar(255) null,
activated tinyint unsigned default 1 not null,
category tinyint unsigned default 0 null,
account_type tinyint 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 not null,
debit decimal(15, 5) default 0.00000 not null,
payment_type int unsigned null,
payment_description text null,
person_delivery_id int unsigned null,
comments text null,
validated int default 0 null,
exported int default 0 null,
registration_date datetime null,
budget_id int unsigned null,
product_id int unsigned null,
signature varchar(56) null,
signature_date bigint unsigned null,
lettering int null,
lettering_date datetime null,
flow_reversal_id int unsigned null,
transaction_id text null
)
comment 'list of account entries';
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 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 not null,
profile_id bigint unsigned not null,
primary key (account_type_id, profile_id)
)
comment 'linked account type field 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 unsigned null,
decimal_precision int unsigned default 2 null
);
create table accounting_profile
(
accounting_id int unsigned not null,
profile_id bigint unsigned not null,
primary key (accounting_id, profile_id)
);
create table activity_type
(
id bigint unsigned 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,
previous_activity_type_id bigint unsigned null,
previous_activity_default_duration int unsigned null,
next_activity_type_id bigint unsigned null,
next_activity_default_duration int unsigned 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,
ref_engine_hours int null,
inspection_date datetime null,
inspection_time int null,
inspection_engine_time int null,
tolerance_time int unsigned default 6000 null,
last_counter int null,
interval_visit_calendar varchar(255) default '50 h' null,
interval_visit_time varchar(255) default '50 h' null,
interval_visit_engine_time varchar(255) default '50 h' null,
time_alert1 int default 6000 null,
time_alert2 int default 0 null,
time_alert3 int default -3000 null,
engine_time_alert1 int default 6000 null,
engine_time_alert2 int default 0 null,
engine_time_alert3 int default 0 null,
day_alert1 smallint default 15 null,
day_alert2 smallint default 0 null,
day_alert3 smallint default -8 null
)
comment 'all aircrafts of all airclubs';
create table aircraft_maintenance_alert
(
id int unsigned auto_increment
primary key,
aircraft_id int unsigned not null,
alert_category tinyint unsigned not null,
alert_level tinyint unsigned not null,
is_enabled tinyint unsigned default 0 not null,
is_sent tinyint unsigned default 0 not null,
unique (aircraft_id, alert_category, alert_level)
);
create table aircraft_type
(
id int unsigned not null
primary key,
flight_time_formula varchar(255) default '%duration' null,
counter_state tinyint 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 unsigned default 4 not null,
engine_duration_formula varchar(255) null
)
comment 'types of aircraft';
create table aircraft_type_allowed_status
(
aircraft_type_id int unsigned not null,
place_num int unsigned null,
status_id int unsigned not null
)
comment 'list of allowed functions for each aircraft type';
create table aircraft_type_mandatory_flight_type
(
aircraft_type_id int unsigned not null,
activity_type_id bigint unsigned not null
)
comment 'list of compulsory flight type for each aircraft type';
create table aircraft_type_uncomp_flight_type
(
aircraft_type_id int unsigned not null,
activity_type_id bigint unsigned not null
)
comment 'list of uncompatible flight type for each aircraft type';
create table aircraft_type_validity_type
(
aircraft_type_id int unsigned not null,
validity_type_id int unsigned 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 not null,
keyword varchar(255) null,
order_num int 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 not null,
credit decimal(15, 5) default 0.00000 not 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
)
comment 'records all the slots of all aircrafts of all airclubs';
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 not null,
activity_type_id bigint unsigned not null,
primary key (booking_id, activity_type_id)
);
create table booking_person
(
booking_id int unsigned not null,
person_id int unsigned 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 unsigned not null,
training_program_id int unsigned not null,
primary key (booking_group_id, training_program_id)
);
create table budget
(
id int unsigned auto_increment
primary key,
group_id int unsigned not null,
name varchar(255) null,
order_num int null,
export_code varchar(255) null
);
create table budget_group
(
id int unsigned auto_increment
primary key,
name varchar(255) null,
order_num int null
);
create table business_field
(
id int unsigned auto_increment
primary key,
variable varchar(255) null,
label varchar(255) not null,
value_type varchar(40) not null,
category varchar(255) not null,
order_num int not null,
dsn varchar(20) default 'customer' null,
compulsory_fill tinyint(1) default 0 null,
linked_category varchar(255) null,
linked_business_field_id int unsigned null,
linked_field_name varchar(255) null,
max_display int default -1 null,
formula text null,
default_value text null,
placeholder text null,
access_level_required tinyint default 0 not null,
user_access_mode tinyint(1) default 0 not null,
unique (variable)
)
comment 'list of extra form field';
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 unsigned 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, 5) default 0.00000 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
);
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 data_retention
(
id int unsigned auto_increment
primary key,
data_processing_title varchar(45) not null,
legal_basis_category int unsigned default 0 not null,
data_retention_period int unsigned not null,
controller_person_id int unsigned 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,
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,
smtp_additional_detail text 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 not null,
start_date datetime null,
end_date datetime null,
presence tinyint unsigned null
);
create table extra_field_profile
(
business_field_id int unsigned not null,
booking_popup_display_4_profile_id bigint unsigned not null,
primary key (business_field_id, booking_popup_display_4_profile_id)
);
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 default 0 not null,
aircraft_type_id int 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 default 0 not null,
activity_type_id bigint unsigned default 0 not null,
excluded tinyint 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 default 0 not null,
place_num tinyint 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 unsigned null,
owner_id int null,
file_group_id int null
);
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 null,
airborne tinyint unsigned null,
validated tinyint unsigned default 0 null,
departure_icao_id varchar(6) null,
arrival_icao_id varchar(6) null,
engine_duration int 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 unsigned default 0 null,
right_account_type tinyint unsigned default 0 null,
credit_budget_id int unsigned null,
debit_budget_id int unsigned null,
order_num int null,
product_id int unsigned null,
sale_trigger_id int unsigned default 0 null,
query text null,
variable_formula varchar(255) 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 unsigned null
);
create table flight_track
(
id int unsigned auto_increment
primary key,
flight_id int not null,
track mediumblob not null,
file_type varchar(255) default 'kml' null
);
create table flight_type_mandatory_validity_type
(
activity_type_id bigint unsigned 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
);
create table ip_stopped
(
id int unsigned auto_increment
primary key,
ip varchar(255) not null,
counter tinyint 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 unsigned default 0 not null
primary key,
key_name tinytext null,
aircraft_id int unsigned default 0 null,
key_state tinyint unsigned default 0 null,
key_word bigint unsigned default 0 null
)
comment 'key assignment description';
create table key_host
(
id tinyint auto_increment
primary key,
timeout tinyint default 10 not null,
num_key tinyint unsigned default 8 not null,
ipkey varchar(50) default '127.0.0.1' not null,
httpport int default 4080 not null
)
comment 'key host configuration';
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 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 null,
weather_station int 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 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 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 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 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 unsigned default 0 not null,
threshold_hours_locked tinyint unsigned default 0 not null
);
create table m_operation
(
id int unsigned auto_increment
primary key,
content text not null,
m_component_type_id int 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 null,
date_work_end date null,
min_overhaul_counter int null,
max_overhaul_counter int null,
min_overhaul_date date null,
max_overhaul_date date null,
min_next_overhaul_counter int null,
max_next_overhaul_counter int null,
min_next_overhaul_date date null,
max_next_overhaul_date date null,
first_reference_visit tinyint unsigned default 0 not null,
is_visit tinyint unsigned default 0 not null,
action_type tinyint unsigned null,
previous_line_linked_time_action int null,
previous_line_linked_calendar_action int null,
next_line_linked_time_action int null,
next_line_linked_calendar_action int null,
time_tolerance int null,
calendar_tolerance int null,
time_periodicity_component int null,
calendar_periodicity_component int null,
time_periodicity int null,
calendar_periodicity int null,
reference_overhaul_counter int 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 null,
calendar_periodicity int null,
time_first_reference int null,
calendar_first_reference date null,
time_tolerance int null,
calendar_tolerance int null,
frame_hours_at_work_start int null,
date_work_end date null,
time_elapsed int null,
calendar_elapsed int null,
time_remaining int null,
calendar_remaining int null,
min_next_overhaul_counter int null,
max_next_overhaul_counter int null,
min_next_overhaul_date date null,
max_next_overhaul_date date null,
effective_hours_at_work_start int null,
theoretical_overhaul_counter int null,
intelligent_overhaul_counter int null,
intelligent_min_next_overhaul_counter int null,
intelligent_max_next_overhaul_counter int 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 unsigned default 0 not null
);
create table maintenance_view
(
id int unsigned default 0 not null
primary key,
frame_total_time int null,
frame_time_since_rg int null,
engine_time_since_rg int null,
propeller_time_since_rg int null,
potential_remaining_next_time_action int null,
potential_remaining_next_time_visit int null,
potential_remaining_next_priority_time_action int null,
potential_remaining_next_calendar_action int null,
potential_remaining_next_calendar_visit int null,
potential_remaining_next_priority_calendar_action int 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 null,
overhaul_counter_visit int 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 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 oauth_access_token
(
id int unsigned auto_increment
primary key,
uid varchar(255) not null,
expiry_date_time datetime not null,
scopes varchar(2048) not null,
oauth_client_id int unsigned not null,
person_id int unsigned null,
unique (id),
unique (uid)
);
create table oauth_auth_code
(
id int unsigned auto_increment
primary key,
uid varchar(255) not null,
expiry_date_time datetime not null,
redirect_uri varchar(2048) not null,
scopes varchar(2048) not null,
oauth_client_id int unsigned not null,
person_id int unsigned not null,
unique (id),
unique (uid)
);
create table oauth_client
(
id int unsigned auto_increment
primary key,
uid varchar(255) not null,
hashed_passphrase varchar(255) null,
name text not null,
authorization_grant_type varchar(255) not null,
redirect_uri varchar(2048) not null,
owner_person_id int unsigned not null,
auth_cert text null,
sign_cert text null,
unique (id),
unique (uid)
);
create table oauth_dpop_token
(
id int unsigned auto_increment
primary key,
jti varchar(255) not null,
expiration_date datetime not null
);
create table oauth_refresh_token
(
id int unsigned auto_increment
primary key,
uid varchar(255) not null,
expiry_date_time datetime not null,
oauth_access_token_uid varchar(255) not null,
scopes varchar(2048) not null,
oauth_client_id int unsigned not null,
person_id int unsigned not null,
dpop_jkt varchar(128) null,
unique (id),
unique (uid)
);
create table parameter
(
code varchar(255) not null,
key_id int unsigned default 0 not null,
enabled tinyint unsigned default 0 null,
int_value bigint unsigned default 0 not null,
char_value mediumtext null,
primary key (code, key_id)
)
comment 'application parameters';
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 unsigned default 0 not null,
pos_key_id int unsigned null,
treasury_account_id int unsigned null,
person_delivery tinyint unsigned default 0 null,
person_budget_id int unsigned null,
treasury_budget_id int unsigned null,
minimum_amount decimal(10, 0) null,
maximum_amount decimal(10, 0) 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 null,
view_type int unsigned null,
view_width tinyint unsigned default 12 not null,
view_height tinyint unsigned default 30 not 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 bigint unsigned null,
activated tinyint unsigned default 1 null,
birthdate datetime default '0000-00-00 00:00:00' null,
sex tinyint 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,
unique (name)
)
comment 'used for authentication';
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 unsigned null
);
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 unsigned default 0 null,
variable_id int null,
sale_type tinyint unsigned null,
is_external_product tinyint(1) null
);
create table profile
(
id bigint unsigned 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 not null,
accounting_id int unsigned not null,
primary key (profile_id, accounting_id)
);
create table profile_extra_field_join
(
profile_id bigint unsigned not null,
business_field_id int unsigned 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 not null,
account_type_id int unsigned not null,
primary key (profile_id, account_type_id)
);
create table profile_resource_type_place
(
profile_id bigint unsigned not null,
resource_type_id int unsigned 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 not null,
validity_type_id int unsigned not null,
manage4oneself int default 0 not null,
certify int 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 not null,
validity_type_id int unsigned 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 record_changelog
(
id int unsigned auto_increment
primary key,
record_id int unsigned not null,
record_type varchar(255) null,
person_id int unsigned null,
changelog_date datetime not null,
record_state text null
);
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 unsigned default 1 not null,
bookable int unsigned default 1 null,
physical int unsigned default 1 null,
color int unsigned null
);
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 unsigned null
);
create table resource_regular_availability
(
id int unsigned auto_increment
primary key,
resource_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_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,
activated tinyint unsigned default 1 not null,
max_booking_duration int default -1 null,
pictogram int unsigned null
);
create table resource_type_place_tag
(
resource_type_id int unsigned not null,
place_num tinyint unsigned not null,
place_tag varchar(255) null,
place_quantity int 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 not 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 not null,
unique (validity_type_id)
);
create index idx_product
on sale_2_validity_type (product_id);
create table sale_pricing
(
id int unsigned auto_increment
primary key,
label varchar(255) null,
price_formula text null,
debit_account_id int unsigned null,
credit_account_id int unsigned null,
debit_account_type tinyint unsigned default 0 null,
credit_account_type tinyint unsigned default 0 null,
credit_budget_id int unsigned null,
debit_budget_id int unsigned null,
order_num int 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,
is_for_external_product tinyint(1) default 1 null,
is_for_internal_product tinyint(1) default 1 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 default 0 not null
primary key,
name varchar(255) null,
event varchar(255) null,
locked tinyint unsigned default 0 not null,
query text null,
validate_entry tinyint(1) default 0 null,
group_sales tinyint(1) default 0 null
);
create table spreadsheet_parameter
(
file_number int 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 default 0 not null,
value decimal(15, 2) default 0.00 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,
carnet_aero_function_id 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 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 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 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 unsigned null,
welcome_cell text null,
business text null,
default_activity_notification bigint unsigned null,
siren int null
)
comment 'description of all airclubs';
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 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 auto_increment
primary key,
uid int null,
email_address varchar(255) null,
supplier_bill_file_id int unsigned not 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,
supplier_name varchar(255) default null,
search_keyword varchar(255) default 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 unsigned null,
start_date datetime default '0000-00-00 00:00:00' null,
activated tinyint 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
);
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 null,
speed double null,
track int unsigned null,
acc_x int null,
acc_y int null,
acc_z int null,
pressure int unsigned null,
battery_level int unsigned null,
gps_fix tinyint unsigned null,
receive_date datetime null
);
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 unsigned auto_increment
primary key,
name varchar(255) not null
);
create table training_activity
(
activity_id int unsigned not null,
training_item_label_id int unsigned default 0 not null,
training_id int unsigned default 1 not null,
level_reached int unsigned null,
comment varchar(255) null,
validated tinyint(1) default 0 not null,
primary key (activity_id, training_item_label_id, training_id)
);
create table training_activity_next_item
(
student_id int unsigned not null,
training_id int unsigned not null,
training_item_label_id int unsigned not null,
reporting_offset tinyint unsigned default 0 not null,
item_preselected tinyint unsigned default 0 not null,
primary key (student_id, training_id, training_item_label_id)
);
create table training_activity_person
(
activity_id int unsigned not null,
person_id int unsigned not null,
num int unsigned default 0 not null,
training_checking_date datetime null,
training_checking_sentence varchar(255) null,
primary key (activity_id, person_id, num)
);
create table training_item
(
training_id int unsigned not null,
training_item_label_id int unsigned not null,
number_of_sessions int not null,
order_num int not null,
custom_label varchar(255) null,
primary key (training_id, training_item_label_id)
);
create table training_phase
(
training_item_order_num int unsigned not null,
training_id int unsigned not null,
name varchar(255) null,
primary key (training_item_order_num, training_id)
);
create table training_program
(
id int unsigned auto_increment
primary key,
training_id int unsigned null,
name varchar(255) not null
);
create table training_program_activity
(
id int unsigned auto_increment
primary key,
training_program_id int unsigned not null,
activity_type_id bigint unsigned not null,
duration int unsigned null,
breaktime int unsigned null,
order_num int unsigned not null
);
create table uncomp_flight_type
(
id1 bigint unsigned not null,
id2 bigint unsigned not null
)
comment 'list of sales';
create table user_reauth_token
(
id int unsigned auto_increment
primary key,
user_id int unsigned not null,
verification_token varchar(255) not null,
ip_address varchar(45) not null,
expiration_date datetime not null,
activated tinyint(1) 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(3) 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 null,
checking_date date not null,
checking_sentence varchar(255) not null,
is_current_validity tinyint(1) default 0 not null,
action_ip_address varchar(255) null,
action_login varchar(255) null,
unique (person_id, validity_type_id, registration_date)
);
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,
alert_on_login int default -2 null,
mandatory_access_control tinyint unsigned default 0 null,
first_reminder_alert int unsigned default 0 null,
reminder_frequency_alert int unsigned default 0 null,
associate_attachment tinyint 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 unsigned default 1 not null
)
comment 'qualifications list';
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 unsigned not null,
unique (validity_type_id, page_index)
);
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
);
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)
);
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 unsigned 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;
create
definer = root@localhost procedure addGapToDate(in gap int)
modifies sql data
BEGIN
DECLARE done TINYINT DEFAULT 0;
DECLARE tmpTableName VARCHAR(255);
DECLARE tmpColumnName VARCHAR(255);
DECLARE cursor1 CURSOR FOR
SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ( SELECT DATABASE() )
AND DATA_TYPE IN ('date', 'datetime');
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cursor1;
REPEAT
FETCH cursor1 INTO tmpTableName, tmpColumnName;
IF NOT done THEN
BEGIN
SET @dynamicQuery = CONCAT('UPDATE ', tmpTableName, ' SET ', tmpColumnName, '= DATE_ADD(', tmpColumnName, ', INTERVAL ', gap, ' DAY)');
PREPARE pQuery FROM @dynamicQuery;
EXECUTE pQuery;
DEALLOCATE PREPARE pQuery;
END;
END IF;
UNTIL done END REPEAT;
CLOSE cursor1;
TRUNCATE TABLE customer_bill_file;
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 : <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>