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