Structure de la base de données : Différence entre versions
(→Structure) |
|||
Ligne 1 : | Ligne 1 : | ||
=Structure= | =Structure= | ||
− | <sql> | + | <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= | =Description= |
Version du 27 août 2020 à 09:42
Structure
create table account ( id int unsigned auto_increment primary key, creation_date date null, name text null, export_account varchar(255) null, activated tinyint(1) unsigned default 1 not null, category tinyint(1) unsigned default 0 null, account_type tinyint(1) unsigned default 0 null, owner_id int unsigned default 0 not null, payment_allowed tinyint(1) default 0 null, budget_id int unsigned null, order_num int null, accounting_id int unsigned null, deactivated_date datetime null, group_sales tinyint(1) default 0 not null ) comment 'List of account'; create index idx_owner_id on account (owner_id); create table account_entry ( id int 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;
Description
Table booking
Le contenu du champ aircraft_id de la table booking peut être nul. Dans ce cas, c'est une ancienne réservation effectuée sur une ressource non-existante ou qui n'existe plus; aussi nommé réservation orpheline.
Table flight
- airborne :
- 0: Pas en l'air ou fermeture de vol : Le pilote a terminé le vol.
- 1: En l'air ou ouverture de vol : Le pilote remplit le vol avant de voler.
Champs cachés
Les champs suivants ne peuvent être exportés and ne doivent pas être inclus dans une requête SELECT. De plus, un "SELECT *" n'est possible quand une des tables contient un champ caché :
- Table structure :
- name
- info_cell
- logo
- logo_name
- logo_ext
- logo_size
- first_hour_displayed
- last_hour_displayed
- usual_profiles
- default_slot_range
- min_slot_range
- twilight_range
- mailing_list_name
- mailing_list_type
- default_timezone
- lang
- admin_num
- mail_from_address
- default_view_type
- address
- zipcode
- city
- state
- country
- phone
- fax
- default_notification
- welcome_cell
- Table journal: Tous les champs
- Table log: Tous les champs
- Table parameter: Tous les champs
- Table person:
- hash_password
Exemple de requêtes non autorisées :
SELECT * FROM structure; SELECT hash_password FROM person; SELECT person.id, validity.* FROM person LEFT JOIN validity ON (person.id=validity.person_id);