Documentation de la solution web de gestion OpenFlyers version 2 - Nouvelles pages [fr]

Structure de la base de données

2017-06-08T11:22:21Z

Jcheng : Page créée avec « =Structure= <sql>CREATE TABLE IF NOT EXISTS `account` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `creation_date` date DEFAULT NULL, `name` text CHARACTER SET... »

=Structure=

<sql>CREATE TABLE IF NOT EXISTS `account` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`creation_date` date DEFAULT NULL,
`name` text CHARACTER SET latin1,
`export_account` text CHARACTER SET latin1,
`activated` int(1) DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of account';

CREATE TABLE IF NOT EXISTS `account_aircraft` (
`account_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
`aircraft_id` int(11) DEFAULT NULL,
`account_type` int(10) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`account_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of aircraft accounts';

CREATE TABLE IF NOT EXISTS `account_aircraft_type` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` text CHARACTER SET latin1,
`activated` tinyint(1) NOT NULL DEFAULT '1',
`order_num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='account aircraft type description';

CREATE TABLE IF NOT EXISTS `account_club` (
`account_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
`payment_allowed` tinyint(1) UNSIGNED DEFAULT '0',
`order_num` int(11) DEFAULT NULL,
PRIMARY KEY (`account_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of club accounts';

CREATE TABLE IF NOT EXISTS `account_entry` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`double_entry_id` int(11) DEFAULT NULL,
`account_date` datetime DEFAULT NULL,
`account_id` int(11) DEFAULT NULL,
`credit` decimal(15,2) DEFAULT '0.00',
`debit` decimal(15,2) DEFAULT '0.00',
`payment_type` int(10) UNSIGNED DEFAULT NULL,
`payment_description` text CHARACTER SET latin1,
`person_delivery_id` int(10) UNSIGNED DEFAULT NULL,
`comments` text CHARACTER SET latin1,
`validated` int(1) DEFAULT '0',
`exported` int(1) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_account_date` (`account_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of account entries';

CREATE TABLE IF NOT EXISTS `account_member` (
`account_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
`member_id` int(11) DEFAULT NULL,
`account_type` int(10) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`account_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of member accounts';

CREATE TABLE IF NOT EXISTS `account_member_type` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` text CHARACTER SET latin1,
`activated` tinyint(1) NOT NULL DEFAULT '1',
`order_num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of account member types';

CREATE TABLE IF NOT EXISTS `aircraft` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`callsign` varchar(255) DEFAULT NULL,
`type` int(10) UNSIGNED DEFAULT NULL,
`comments` varchar(255) DEFAULT NULL,
`non_bookable` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`ref_date` datetime DEFAULT NULL,
`ref_hours` int(11) DEFAULT NULL,
`inspection_date` datetime DEFAULT NULL,
`inspection_time` int(11) DEFAULT NULL,
`interval_visit` int(11) UNSIGNED DEFAULT '30000',
`tolerance_time` int(11) UNSIGNED DEFAULT '6000',
`last_counter` int(11) DEFAULT NULL,
`activated` tinyint(1) UNSIGNED DEFAULT '1',
`time_alert1` int(11) DEFAULT '6000',
`time_alert2` int(11) DEFAULT '0',
`time_alert3` int(11) DEFAULT '0',
`day_alert1` smallint(3) DEFAULT '15',
`day_alert2` smallint(3) DEFAULT '0',
`day_alert3` smallint(3) DEFAULT '8',
`non_flightable` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
`order_num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `CALLSIGN` (`callsign`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='ALL AIRCRAFTS OF ALL AIRCLUBS';

CREATE TABLE IF NOT EXISTS `aircraft_type` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`admin_desc` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`seats_available` tinyint(3) UNSIGNED DEFAULT '1',
`comments` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`flight_time_formula` varchar(255) CHARACTER SET latin1 DEFAULT '%DURATION',
`counter_state` tinyint(2) NOT NULL DEFAULT '-1',
`tolerance` int(11) DEFAULT '0',
`autonomy` int(11) DEFAULT '5990',
`activated` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
`digit_counter_number` tinyint(1) UNSIGNED NOT NULL DEFAULT '4',
`order_num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Types of aircraft';

CREATE TABLE IF NOT EXISTS `aircraft_type_allowed_function` (
`aircraft_type_id` int(10) UNSIGNED DEFAULT NULL,
`place_num` int(10) UNSIGNED DEFAULT NULL,
`function_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of allowed functions for each aircraft type';

CREATE TABLE IF NOT EXISTS `aircraft_type_mandatory_flight_type` (
`aircraft_type_id` int(10) UNSIGNED DEFAULT NULL,
`flight_type_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of compulsory flight type for each aircraft type';

CREATE TABLE IF NOT EXISTS `aircraft_type_qualif` (
`aircraft_type_id` int(10) UNSIGNED DEFAULT NULL,
`check_num` int(10) UNSIGNED DEFAULT NULL,
`qualification_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Types of qualif required for each aircraft type';

CREATE TABLE IF NOT EXISTS `aircraft_type_uncomp_flight_type` (
`aircraft_type_id` int(10) UNSIGNED DEFAULT NULL,
`flight_type_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of uncompatible flight type for each aircraft type';

CREATE TABLE IF NOT EXISTS `authentication` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`profile` mediumint(8) UNSIGNED DEFAULT NULL,
`view_type` int(10) UNSIGNED DEFAULT NULL,
`view_width` tinyint(3) UNSIGNED NOT NULL DEFAULT '12',
`view_height` tinyint(4) UNSIGNED NOT NULL DEFAULT '30',
`aircrafts_viewed` varchar(255) DEFAULT NULL,
`inst_viewed` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`timezone` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`zipcode` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`state` varchar(255) DEFAULT NULL,
`country` varchar(255) DEFAULT NULL,
`home_phone` varchar(255) DEFAULT NULL,
`work_phone` varchar(255) DEFAULT NULL,
`cell_phone` varchar(255) DEFAULT NULL,
`lang` varchar(255) DEFAULT NULL,
`notification` tinyint(3) UNSIGNED DEFAULT NULL,
`activated` tinyint(1) UNSIGNED DEFAULT '1',
`birthdate` datetime DEFAULT '0000-00-00 00:00:00',
`sex` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`nationality` char(2) DEFAULT NULL,
`total_flight_time` int(10) UNSIGNED DEFAULT '0',
`date_total_flight_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='used for authentication';

CREATE TABLE IF NOT EXISTS `balance` (
`account_id` int(10) UNSIGNED NOT NULL,
`balance_date_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
`debit` decimal(15,2) DEFAULT '0.00',
`credit` decimal(15,2) DEFAULT '0.00',
PRIMARY KEY (`account_id`,`balance_date_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `balance_date` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`balance_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `booking` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`start_date` datetime DEFAULT NULL,
`end_date` datetime DEFAULT NULL,
`aircraft_id` int(10) UNSIGNED DEFAULT NULL,
`member_id` int(10) UNSIGNED DEFAULT NULL,
`slot_type` tinyint(3) UNSIGNED DEFAULT NULL,
`instructor_id` int(10) UNSIGNED DEFAULT NULL,
`free_seats` tinyint(3) UNSIGNED DEFAULT NULL,
`comments` varchar(255) DEFAULT NULL,
`airfield` varchar(6) DEFAULT NULL,
`estimated_duration` int(10) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='RECORDS ALL THE SLOTS OF ALL AIRCRAFTS OF ALL AIRCLUBS';

CREATE TABLE IF NOT EXISTS `club` (
`id` int(10) NOT NULL DEFAULT '0',
`name` varchar(255) DEFAULT NULL,
`info_cell` text,
`logo` longblob,
`logo_name` varchar(255) DEFAULT NULL,
`logo_ext` varchar(25) DEFAULT NULL,
`logo_size` int(11) DEFAULT NULL,
`first_hour_displayed` time DEFAULT NULL,
`last_hour_displayed` time DEFAULT NULL,
`usual_profiles` mediumint(8) UNSIGNED DEFAULT NULL,
`icao` varchar(6) DEFAULT NULL,
`default_slot_range` int(10) UNSIGNED DEFAULT NULL,
`min_slot_range` tinyint(3) UNSIGNED DEFAULT NULL,
`twilight_range` tinyint(3) UNSIGNED DEFAULT NULL,
`mailing_list_name` varchar(255) DEFAULT NULL,
`mailing_list_type` varchar(255) DEFAULT NULL,
`club_site_url` varchar(255) DEFAULT NULL,
`default_timezone` varchar(255) NOT NULL,
`lang` varchar(255) NOT NULL,
`admin_num` int(10) UNSIGNED NOT NULL,
`mail_from_address` varchar(255) DEFAULT NULL,
`default_view_type` int(10) UNSIGNED DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`zipcode` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`state` varchar(255) DEFAULT NULL,
`country` varchar(255) DEFAULT NULL,
`phone` varchar(255) DEFAULT NULL,
`fax` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`default_notification` int(3) UNSIGNED DEFAULT NULL,
`welcome_cell` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='DESCRIPTION OF ALL AIRCLUBS';

CREATE TABLE IF NOT EXISTS `criteria` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`label` varchar(255) CHARACTER SET latin1 NOT NULL,
`predicate` text CHARACTER SET latin1 NOT NULL,
`order_num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `default_display` (
`authentication_id` int(10) UNSIGNED NOT NULL,
`display_key` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
`display_value` text CHARACTER SET latin1,
PRIMARY KEY (`authentication_id`,`display_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `exceptionnal_inst_date` (
`instructor_id` int(10) UNSIGNED NOT NULL,
`start_date` datetime DEFAULT NULL,
`end_date` datetime DEFAULT NULL,
`presence` tinyint(1) UNSIGNED DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `favorite_icao` (
`icao` varchar(6) NOT NULL,
PRIMARY KEY (`icao`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='favorite airfield list';

CREATE TABLE IF NOT EXISTS `fhp_aircraft_type` (
`fhp_id` int(10) DEFAULT NULL,
`aircraft_type_id` int(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='flight hour pricing aircraft type list';

CREATE TABLE IF NOT EXISTS `fhp_flight_type` (
`fhp_id` int(10) DEFAULT NULL,
`flight_type_id` int(10) DEFAULT NULL,
`excluded` tinyint(1) UNSIGNED DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='flight hour pricing flight type list';

CREATE TABLE IF NOT EXISTS `fhp_member_pool` (
`fhp_id` int(10) DEFAULT NULL,
`member_pool_id` int(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='flight hour pricing flight type list';

CREATE TABLE IF NOT EXISTS `flight` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`aircraft_id` int(10) UNSIGNED DEFAULT NULL,
`start_date` datetime DEFAULT NULL,
`duration` int(10) UNSIGNED DEFAULT NULL,
`flight_type_id` int(10) UNSIGNED DEFAULT NULL,
`people_onboard` int(10) UNSIGNED DEFAULT NULL,
`airfield_departure` varchar(6) CHARACTER SET latin1 DEFAULT NULL,
`airfield_arrival` varchar(6) CHARACTER SET latin1 DEFAULT NULL,
`counter_departure` int(10) UNSIGNED DEFAULT NULL,
`counter_arrival` int(10) UNSIGNED DEFAULT NULL,
`landing_number` int(10) UNSIGNED DEFAULT NULL,
`comments` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`airborne` tinyint(1) UNSIGNED DEFAULT NULL,
`validated` tinyint(3) UNSIGNED DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='list of flight';

CREATE TABLE IF NOT EXISTS `flight_account_entry` (
`flight_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
`account_entry_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`flight_id`,`account_entry_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='flight account entry join';

CREATE TABLE IF NOT EXISTS `flight_hours_pricing` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` text CHARACTER SET latin1,
`price_formula` text CHARACTER SET latin1,
`price_sentence` text CHARACTER SET latin1,
`left_account_id` int(10) UNSIGNED DEFAULT NULL,
`right_account_id` int(10) UNSIGNED DEFAULT NULL,
`left_account_type` tinyint(1) UNSIGNED DEFAULT '0',
`right_account_type` tinyint(1) UNSIGNED DEFAULT '0',
`member_debited` tinyint(1) UNSIGNED DEFAULT '1',
`order_num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='list of flight hours pricing formula';

CREATE TABLE IF NOT EXISTS `flight_pilot` (
`flight_id` int(10) UNSIGNED NOT NULL,
`pilot_id` int(10) UNSIGNED NOT NULL,
`function_id` int(10) UNSIGNED DEFAULT NULL,
`num` int(10) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`flight_id`,`pilot_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='list of crew for each flight';

CREATE TABLE IF NOT EXISTS `flight_tank_qty` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`flight_id` int(10) UNSIGNED NOT NULL,
`tank_id` int(10) UNSIGNED NOT NULL,
`quantity` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '0.00',
`after_flight` tinyint(1) NOT NULL DEFAULT '0',
`account_id` int(10) UNSIGNED DEFAULT NULL,
`pay_type` tinyint(1) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `flight_type` (
`id` int(10) UNSIGNED NOT NULL,
`name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`order_num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='list of flight type';

CREATE TABLE IF NOT EXISTS `flight_type_mandatory_qualification` (
`flight_type_id` int(10) UNSIGNED NOT NULL,
`qualification_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`flight_type_id`,`qualification_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='list of mandatory qualification for each flight type';

CREATE TABLE IF NOT EXISTS `icao` (
`name` varchar(64) DEFAULT NULL,
`icao` varchar(6) NOT NULL,
`lat` float DEFAULT NULL,
`lon` float DEFAULT NULL,
`alt` smallint(6) DEFAULT NULL,
PRIMARY KEY (`icao`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='airfields coord';

CREATE TABLE IF NOT EXISTS `instructor` (
`id` int(10) UNSIGNED NOT NULL,
`sign` varchar(255) DEFAULT NULL,
`order_num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='LIST INSTRUCTORS AND THEIR AUTORIZATIONS';

CREATE TABLE IF NOT EXISTS `ip_stopped` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`ip` varchar(255) NOT NULL,
`counter` tinyint(1) UNSIGNED NOT NULL,
`expire_date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='BLACKLISTED IP';

CREATE TABLE IF NOT EXISTS `journal` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`login` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`date_log` datetime DEFAULT NULL,
`rights` text CHARACTER SET latin1,
`rights2` text CHARACTER SET latin1,
`is_member` tinyint(1) UNSIGNED DEFAULT NULL,
`is_instructor` tinyint(1) UNSIGNED DEFAULT NULL,
`action` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_date_log` (`date_log`),
KEY `idx_action` (`action`(8))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='list of logs';

CREATE TABLE IF NOT EXISTS `key_assignment` (
`key_id` tinyint(2) UNSIGNED NOT NULL DEFAULT '0',
`key_name` tinytext CHARACTER SET latin1,
`aircraft_id` int(10) UNSIGNED DEFAULT '0',
`key_state` tinyint(1) UNSIGNED DEFAULT '0',
`key_word` bigint(20) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`key_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='key assignment description';

CREATE TABLE IF NOT EXISTS `key_host` (
`id` tinyint(2) NOT NULL AUTO_INCREMENT,
`timeout` tinyint(2) UNSIGNED NOT NULL DEFAULT '10',
`num_key` tinyint(2) UNSIGNED NOT NULL DEFAULT '10',
`ipkey` varchar(50) CHARACTER SET latin1 NOT NULL,
`httpport` varchar(4) CHARACTER SET latin1 NOT NULL DEFAULT '4080',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='key host configuration';

CREATE TABLE IF NOT EXISTS `log` (
`journal_id` int(10) UNSIGNED NOT NULL,
`action` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`table_name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`field_name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`field_value` text CHARACTER SET latin1,
KEY `journal_id` (`journal_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='part of logs';

CREATE TABLE IF NOT EXISTS `login_stopped` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`login` varchar(255) NOT NULL,
`counter` tinyint(3) UNSIGNED NOT NULL,
`expire_date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='BLACKLISTED LOGIN';

CREATE TABLE IF NOT EXISTS `member` (
`id` int(10) UNSIGNED NOT NULL,
`member_num` int(11) DEFAULT NULL,
`subscription` date NOT NULL DEFAULT '2003-12-31',
`qualif_alert_delay` tinyint(3) UNSIGNED NOT NULL DEFAULT '8',
`inscription_date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='ALL MEMBERS OF ALL AIRCLUBS';

CREATE TABLE IF NOT EXISTS `member_awaiting_activation` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`login` varchar(255) CHARACTER SET latin1 NOT NULL,
`ip` varchar(255) CHARACTER SET latin1 NOT NULL,
`code` varchar(255) CHARACTER SET latin1 NOT NULL,
`category` varchar(255) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `member_pool` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` text CHARACTER SET latin1,
`required_account_type` int(10) UNSIGNED DEFAULT NULL,
`order_num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of member pool';

CREATE TABLE IF NOT EXISTS `member_pool_join` (
`member_pool_id` int(10) UNSIGNED DEFAULT NULL,
`member_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Correspondance between pools and members';

CREATE TABLE IF NOT EXISTS `member_qualif` (
`member_id` int(10) UNSIGNED NOT NULL,
`qualif_id` int(10) UNSIGNED NOT NULL,
`expire_date` date DEFAULT NULL,
`no_alert` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`ident_value` varchar(255) DEFAULT NULL,
`grant_date` date DEFAULT NULL,
PRIMARY KEY (`member_id`,`qualif_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='QUALIFICATIONS OF EACH MEMBER';

CREATE TABLE IF NOT EXISTS `nationality` (
`code` char(2) NOT NULL DEFAULT '',
`label` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `parameter` (
`code` varchar(255) NOT NULL,
`enabled` tinyint(1) UNSIGNED DEFAULT '0',
`int_value` int(10) UNSIGNED DEFAULT '0',
`char_value` varchar(255) DEFAULT NULL,
PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='APPLICATION PARAMETERS';

CREATE TABLE IF NOT EXISTS `payment_distribution` (
`payment_id` int(10) UNSIGNED DEFAULT NULL,
`account_club_id` int(10) UNSIGNED DEFAULT NULL,
`person_delivery` tinyint(1) UNSIGNED DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='payment distribution description';

CREATE TABLE IF NOT EXISTS `payment_type` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` text CHARACTER SET latin1,
`text_field_label` text CHARACTER SET latin1,
`supplier` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`only_admin` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`order_num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='type of payment description';

CREATE TABLE IF NOT EXISTS `pilot_function` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`abbrev` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
`name` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`order_num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of functions for pilot';

CREATE TABLE IF NOT EXISTS `pricing_value` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`variable_id` int(11) NOT NULL,
`assign_value` decimal(10,2) NOT NULL,
`start_date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `pricing_variable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`variable` varchar(255) CHARACTER SET latin1 NOT NULL,
`label` varchar(255) CHARACTER SET latin1 NOT NULL,
`order_num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `profile` (
`id` int(10) UNSIGNED NOT NULL DEFAULT '0',
`name` varchar(255) DEFAULT NULL,
`permits` int(10) UNSIGNED DEFAULT NULL,
`permits2` int(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `psp_transaction` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`credit_account_id` int(10) UNSIGNED NOT NULL,
`debit_account_id` int(10) UNSIGNED NOT NULL,
`transaction_date` datetime NOT NULL,
`amount` float NOT NULL,
`description` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`payment_type_id` int(10) UNSIGNED NOT NULL,
`state` int(10) UNSIGNED NOT NULL DEFAULT '0',
`bank_answer` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`bank_misc` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `qualification` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`time_limitation` tinyint(1) NOT NULL DEFAULT '0',
`ident_value_enable` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`grant_date_enable` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`is_for_instructor` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`mandatory` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`experience_formula` varchar(255) DEFAULT NULL,
`order_num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='QUALIFICATIONS LIST';

CREATE TABLE IF NOT EXISTS `regular_presence_inst_date` (
`instructor_id` int(10) UNSIGNED DEFAULT NULL,
`start_day` tinyint(3) UNSIGNED DEFAULT NULL,
`end_day` tinyint(3) UNSIGNED DEFAULT NULL,
`start_hour` time DEFAULT NULL,
`end_hour` time DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `sale` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`entry_id` int(10) UNSIGNED DEFAULT NULL,
`unit_price` float DEFAULT NULL,
`quantity` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of sales';

CREATE TABLE IF NOT EXISTS `tank` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`aircraft_type_id` int(10) UNSIGNED NOT NULL,
`tank_type_id` int(10) UNSIGNED NOT NULL,
`unit_id` int(10) UNSIGNED NOT NULL,
`label` varchar(255) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `uncomp_flight_type` (
`id1` int(10) UNSIGNED DEFAULT NULL,
`id2` int(10) UNSIGNED DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List of sales';</sql>

Contrôle d'identification par OpenFlyers pour un logiciel tiers

2016-06-16T10:30:07Z

Bbarbe : /* Présentation */

=Présentation=
Cette page explique comment vérifier qu'un couple identifiant/mot de passe envoyé, par vos propres scripts, est conforme à la base de données d'OpenFlyers.

Le script retourne une valeur indiquant si la connexion, avec des identifiants données, a réussi et son état. Un cookie OpenFlyers est aussi retourné, permettant de gérer une session utilisateur sur votre site, en utilisant le compte utilisateur OpenFlyers de l'utilisateur connecté.

=Comment ça marche=
Si votre plateforme OpenFlyers se situe sur le lien http://openflyers.com/nom-plateforme/, envoyez simplement une requête POST sur le lien http://openflyers.com/nom-plateforme/checkIdent.php avec comme paramètres les variables '''login''' et '''rawPassword'''.

'''Attention:''' Les versions 2 d'OpenFlyers ou plus nécessitent un mot de passe chiffré en MD5 (cf. la ligne $postData commentée ci-dessous, dans le script PHP).

==Valeurs de retour possibles==
Le script retourne un chiffre parmi les suivant :
*0: OK
*1: OK mais plusieurs profils disponibles. OpenFlyers sélectionne automatiquement le meilleur profil.
*2: Expiré mais autorisé
*3: Expiré mais autorisé, avec un profil expiré
*4: Abonnement expiré, refusé
*5: Mauvais identifiants, refusé
*6: IP ou identifiants bloqués, refusé
*7: Aucun identifiant donné, ils sont demandés

Nous vous recommandons de considérer un code de retour entre 0 et 2 comme bon et mauvais entre 3 et 7.

'''Attention:''' Vous devez filtrer les identifiants de connexion libres (sans droits) puisque pour OpenFlyers, ils correspondent à des accès autorisés !!!

=JavaScript=
Si vous utilisez votre propre formulaire d'authentification, utilisez la fonction javascript submit_pwd() située dans \javascript\submitPwd.js .

=Exemple de code PHP=
Voici un exemple de code PHP permettant d'envoyer une requête POST :
<php>function httpPostRequest($host, $path, $postData) {
$result= "";

$request = "POST $path HTTP/1.1\n".
"Host: $host\n".
(isset($referer) ? "Referer: $referer\n" : "").
"Content-type: Application/x-www-form-urlencoded\n".
"Content-length: ".strlen($postData)."\n".
"Connection: close\n\n".
$postData."\n";

// Some debug informations:
// print("<pre>Request:\n".htmlentities($request)."</pre>");

if ($fp = fsockopen($host, 80, $errno, $errstr, 3))
// for PHP release < 5.3.0, use the following syntax:
// if ($fp = fsockopen($host, 80, &$errno, &$errstr, 3))
{
if (fputs($fp, $request))
{
while(! feof($fp))
{
$result.= fgets($fp, 128);
}
fclose($fp);
// print($result);
return $result;
}
}
}

//$postData='login=jbond&rawPassword='.md5('007'); // for OpenFlyers release 2 or higher
$postData='login=jbond&rawPassword=007';
$rawContent = httpPostRequest('openflyers.com','http://openflyers.com/plateform-name/checkIdent.php',$postData); [^]

list($header, $content) = explode("\r\n\r\n", $rawContent, 2);
list($byteQty, $realContent, $dummy) = explode("\r\n", $content, 3);

// the answer is in $realContent</php>

=Plugin d'authentification Joomla=
Si vous avez un site Joomla et que vous désirer de permettre aux utilisateurs OpenFlyers de se connecter à votre espace restreint Joomla, vous devriez ajouter ce plugin de manière à avoir une unique base de données de comptes utilisateurs : celle d'OpenFlyers.

Vous n'avez pas besoin de mettre à jour votre base de données Joomla, ce plugin interroge directement OpenFlyers grâce au script PHP CheckIdent.php.

*[[Media:Joomla_authentication_plugin.zip|Joomla plugin for OpenFlyers]]

Bibliothèque_des_rapports

2016-02-14T22:02:05Z

Claratte : /* Users with validities viewer */

=Users=
<sql>SELECT id, last_name, first_name
FROM person
WHERE activated=1</sql>

=Users and <e-mail>=
<sql>SELECT CONCAT(person.first_name, ' ', person.last_name, ' <', person.email, '>') AS emails
FROM person
WHERE person.activated=1
ORDER BY person.last_name, person.first_name</sql>

=Users by profile=
Variable $profile should be defined first and should be of dbOject::Profile value type.

<sql>SELECT
last_name AS Lastname,
first_name AS Firstname,
profile.name AS Profile
FROM person
LEFT JOIN profile ON (person.profile & profile.id)
WHERE person.activated = 1 AND profile.id = $profile
ORDER BY Profile, Lastname, Firstname</sql>

=Users by validity=
Following extra field required:
*validityTypeId (Type: dbObject:ValidityType)

<sql>SELECT validity_type.name AS 'Validity',
DATE_FORMAT(grant_date,'%d/%m/%Y') AS Date_d_obtention,
last_name AS Name,
first_name AS Firstname,
ident_value AS Comment
FROM validity_type
LEFT JOIN validity ON validity.validity_type_id =validity_type.id
LEFT JOIN person ON person.id=validity.person_id
WHERE validity_type.id='$validityTypeId'
ORDER BY Name, Firstname</sql>

=Users by validity obtained after the selected year=

* Variable '''$year''' should be defined first and should be of '''Year''' value type.

<sql>SELECT validity_type.name AS 'Validity',
DATE_FORMAT(grant_date,'%d/%m/%Y') AS Date,
last_name AS Name,
first_name AS Firstname,
ident_value AS Comment
FROM validity_type
LEFT JOIN validity ON validity.validity_type_id=validity_type.id
LEFT JOIN person ON person.id=validity.person_id
WHERE YEAR(grant_date)>$year
ORDER BY 'Validity', Name, Firstname</sql>

=Users with address=
<sql>SELECT
last_name,
first_name,
name AS login,
email,
address,
zipcode,
city AS Ville,
state AS etat_region,
country AS pays,
home_phone AS tel_domicile,
work_phone AS tel_travail,
cell_phone AS tel_mobile
FROM person
WHERE activated=1
ORDER BY last_name,first_name</sql>

=Users with address and registration date (using ExtraField)=
Extra field required :
#registrationDate
#*label : Registration date
#*category : User
#*value type : DateTime

Validity type required :
#Cotisation

<sql>SELECT
last_name,
first_name,
email,
address,
zipcode,
city,
state,
country,
home_phone,
work_phone,
cell_phone,
sex,
DATE_FORMAT(birthdate, '%Y-%m-%d') AS birthdate,
nationality,
validity.grant_date AS subscription_date,
(
SELECT DATE_FORMAT(extra_field_content.content, '%Y-%m-%d')
FROM extra_field
LEFT JOIN extra_field_content ON extra_field.id=extra_field_content.extra_field_id
WHERE extra_field.variable="registrationDate" and extra_field_content.category_id=person.id
)AS registration_date
FROM person
LEFT JOIN validity ON (validity.person_id = person.id)
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
WHERE activated = 1 AND validity_type.name = 'Cotisation'
ORDER BY last_name, first_name</sql>

=Users with address and registration date (using Validity)=
<sql>SELECT
person.last_name,
person.first_name,
validity.grant_date AS registration_date
FROM person
LEFT JOIN validity ON validity.person_id=person.id
LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id
WHERE validity_type.name = "Date d'inscription" AND person.activated=1
GROUP BY last_name, first_name</sql>

=Users with e-mails of selected validity type person equals given year=
Following extra field required:
*validityTypeId (Type: dbObject:ValidityType)
*year (Type: Year)

<sql>SELECT CONCAT(person.first_name, ' ', person.last_name, ' <', person.email, '>') AS emails
FROM person
LEFT JOIN validity ON (validity.person_id = person.id)
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
WHERE person.activated=1 AND YEAR(validity.expire_date) = $year AND validity_type.id = '$validityTypeId'
ORDER BY person.last_name, person.first_name</sql>

=Users with e-mails with up-to-date expire date of validity VVV=
Following symbols should be replace:
*VVV : validity type id

<sql>SELECT CONCAT(person.first_name, ' ', person.last_name, ' <', person.email, '>') AS emails
FROM person
LEFT JOIN validity ON (validity.person_id = person.id)
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
WHERE person.activated=1 AND validity.expire_date > NOW() AND validity_type.id = VVV
ORDER BY last_name,first_name</sql>

=Users with validity ending before a specific date=
Following extra field required:
*endDate (Type: Date)
*validityTypeId (Type: dbObject:ValidityType)

<sql>SELECT person.id, person.first_name, person.last_name, validity_type.name AS 'Validity'
FROM person
LEFT JOIN validity ON (validity.person_id = person.id)
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
WHERE person.activated=1 AND validity.grant_date <= '$endDate'
AND validity_type.id = '$validityTypeId'</sql>

=Users with up-to-date validity X and Y and user details=
Replace X and Y by the validity_type id (2 times).
<sql>SELECT CONCAT(last_name, ' ', first_name) AS 'User',
validity_type.name AS 'Validity name',
expire_date AS 'Expire date',
DATE_FORMAT(birthdate, '%Y/%m/%d') AS 'Birthdate',
email,
CONCAT (address, ' ', zipcode, ' ', city, ' ', state, ' ', country) AS 'Adress',
home_phone AS 'Home phone',
work_phone AS 'Work phone',
cell_phone AS 'Cell phone'
FROM person
LEFT JOIN validity ON person.id=validity.person_id
LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id
WHERE validity_type.time_limitation=1 AND (validity_type.id=X OR validity_type.id=Y)
AND person.id IN
( SELECT person.id FROM person
LEFT JOIN validity ON person.id=validity.person_id
LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id
WHERE person.activated=1 AND (validity_type.id=X OR validity_type.id=Y) AND validity.expire_date >= UTC_DATE() )
ORDER BY last_name, first_name, validity_type.name</sql>

=Utilisateurs avec leurs validités=
<sql>SELECT
last_name AS "Nom",
first_name AS "Prenom",
validity_type.name AS "Validité",
DATE_FORMAT(expire_date,'%d/%m/%Y') AS "Date expiration",
IF((DATEDIFF(DATE(expire_date),DATE(Now())) < 0), "Expired","") AS "Perimee",
DATE_FORMAT(grant_date,'%d/%m/%Y') AS "Date obtention"
FROM validity_type
LEFT JOIN validity ON validity.validity_type_id = validity_type.id
LEFT JOIN person ON person.id=validity.person_id
WHERE person.activated = 1
ORDER BY Last_name, First_name, validity_type.name</sql>

=Users with age and birth date=
<sql>(SELECT last_name AS Last_name,

first_name AS First_name,

DATE_FORMAT(birthdate ,'%d-%m-%Y') AS Birth_date,

IF ( (sex = 0), 'Man', 'Woman' ) AS sex,

(DATE_FORMAT(DATE('$day'), '%Y') -
DATE_FORMAT(birthdate, '%Y') -
(SELECT DATE_FORMAT(DATE('$day'), '00-%m-%d') < DATE_FORMAT( birthdate, '00-%m-%d'))) AS 'age',

DATE_FORMAT(DATE('$day'),'%d-%m-%Y') AS on_date,

IF((DATE_FORMAT(DATE('$day'), '%Y') -
DATE_FORMAT(birthdate, '%Y') -
(SELECT DATE_FORMAT(DATE('$day'), '00-%m-%d') < DATE_FORMAT( birthdate, '00-%m-%d')))>=21, '>= 21', '< 21') AS Major,

-- inscription_date,

-- member.subscription,

-- entry.account_date as Adhesion
DATE_FORMAT(entry.account_date,'%d-%m-%Y') as Grant_date

-- ,MAX(entry.account_date)

-- , $day
-- , entry.flow_id
-- , account.name

FROM person

RIGHT JOIN validity ON validity.person_id = person.id

RIGHT JOIN account acc2 on acc2.owner_id = person.id

RIGHT JOIN account_entry linked_entry on linked_entry.account_id = acc2.id

RIGHT JOIN account_entry entry on linked_entry.flow_id = entry.flow_id

RIGHT JOIN account on entry.account_id = account.id

WHERE year(validity.grant_date) = $year

AND account.name = 'Cotisations ACB'

AND (year(entry.account_date)=$year OR (year(entry.account_date)=$year -1 AND month(entry.account_date)=12) )

AND entry.account_date < DATE('$day')

ORDER BY Last_name LIMIT 999999
)
UNION
(
SELECT COUNT(*) AS Last_name,

COUNT(*) AS First_name,

COUNT(*) AS Birth_date,

COUNT(*) AS sex,

COUNT(*) AS 'age',

COUNT(*) AS on_date,

COUNT(*) AS Major,

COUNT(*) as Grant_date

FROM person

RIGHT JOIN validity ON validity.person_id = person.id

RIGHT JOIN account acc2 on acc2.owner_id = person.id

RIGHT JOIN account_entry linked_entry on linked_entry.account_id = acc2.id

RIGHT JOIN account_entry entry on linked_entry.flow_id = entry.flow_id

RIGHT JOIN account on entry.account_id = account.id

WHERE year(validity.grant_date) = $year

AND account.name = 'Cotisations ACB'

AND (year(entry.account_date)=$year OR (year(entry.account_date)=$year -1 AND month(entry.account_date)=12) )

AND entry.account_date < DATE('$day')
)</sql>

=Users with validity grant date and profiles=
Following extra field required:
*validityTypeId (Type: dbObject:ValidityType)

<sql>SELECT validity_type.name AS 'Validity',
DATE_FORMAT(grant_date, '%d/%m/%Y') AS grant_date,
last_name AS Name,
first_name AS Firstname,
ident_value AS Comment,
( SELECT GROUP_CONCAT(profile.name SEPARATOR ', ')
FROM profile WHERE (person.profile & profile.id)
)AS Profile
FROM validity_type
LEFT JOIN validity ON validity.validity_type_id=validity_type.id
LEFT JOIN person ON person.id=validity.person_id
WHERE YEAR(grant_date)>=$year
AND validity_type.id='$validityTypeId'
ORDER BY Name, Firstname</sql>

=Users with registration date, profiles and total flight time=
<sql>SELECT
person.last_name,
person.first_name,
validity.grant_date AS registration_date,
person.birthdate AS birthdate,
IF ( (person.sex = 0), 'M', 'F' ) AS Gender,
(
SELECT GROUP_CONCAT(profile.name SEPARATOR ', ')
FROM profile WHERE (person.profile & profile.id)
)AS Profile,
IFNULL((
SELECT
CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total
FROM flight_pilot fp
LEFT JOIN flight ON fp.flight_id=flight.id
WHERE fp.pilot_id=person.id AND fp.num=0
), 0) AS Total_flight_time
FROM person
LEFT JOIN validity ON validity.person_id=person.id
LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id
WHERE validity_type.name = "Date d'inscription" AND person.activated=1
GROUP BY last_name, first_name</sql>

=Users with email=
<sql>SELECT last_name, first_name, email
FROM person
WHERE activated=1
ORDER BY last_name, first_name</sql>

=Users with expire date validity equals a given year=
Following extra field required:
*validityTypeId (Type: dbObject:ValidityType)
*year (Type: Year)

<sql>SELECT person.id, person.first_name, person.last_name, validity_type.name AS 'Validity'
FROM person
LEFT JOIN validity ON (validity.person_id = person.id)
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
WHERE person.activated=1 AND YEAR(validity.expire_date) = $year AND validity_type.id = '$validityTypeId'</sql>

=Users with expire date validities inferior to the first day of the given month and year=

Following extra field required:
* month (Type: integer)
* year (Type: year)

<sql>SELECT CONCAT(last_name, ' ', first_name) AS 'User', validity_type.name AS 'Validity name', expire_date AS 'Expire date'
FROM person
LEFT JOIN validity ON person.id=validity.person_id
LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id
WHERE validity_type.time_limitation=1
AND validity.expire_date < '$year-$month-01'
AND person.activated=1
ORDER BY last_name, first_name, validity_type.name</sql>

=Users with expire date validities inferior to the first day of the given month and year, with up-to-date expire date of validity XX=

Following extra field required:
* month (Type: integer)
* year (Type: year)
Following symbols should be replace:
* XX : validity type id

<sql>SELECT CONCAT(last_name, ' ', first_name) AS 'User', validity_type.name AS 'Validity name', expire_date AS 'Expire date'
FROM person
LEFT JOIN validity ON person.id=validity.person_id
LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id
WHERE validity_type.time_limitation=1
AND validity.expire_date < '$year-$month-01'
AND person.id IN (
SELECT person.id
FROM person
LEFT JOIN validity ON person.id=validity.person_id
LEFT JOIN validity_type ON validity_type.id=validity.validity_type_id
WHERE person.activated=1
AND validity_type.id=XX
AND validity.expire_date >= UTC_DATE()
)
ORDER BY last_name, first_name, validity_type.name</sql>

=Users without required validities=
<sql>SELECT CONCAT(person.last_name, ' ', person.first_name) AS pilot, validity_type.name AS validity_name
FROM flight_type_mandatory_validity_type
LEFT JOIN flight_type ON flight_type.id = flight_type_mandatory_validity_type.flight_type_id
LEFT JOIN flight ON flight.flight_type_id & flight_type.id
LEFT JOIN resource ON resource.id = flight.aircraft_id
LEFT JOIN aircraft_type ON resource.resource_type_id = aircraft_type.id
LEFT JOIN aircraft_type_validity_type ON aircraft_type.id = aircraft_type_validity_type.aircraft_type_id
LEFT JOIN flight_pilot ON flight.id = flight_pilot.flight_id
LEFT JOIN person ON person.id = flight_pilot.pilot_id
LEFT JOIN validity_type ON (validity_type.id = flight_type_mandatory_validity_type.validity_type_id OR validity_type.id = aircraft_type_validity_type.validity_type_id)
WHERE flight.airborne = 0
AND ROW(person.id, validity_type.id) NOT IN (SELECT person_id, validity_type_id FROM validity)
AND validity_type.experience_formula IS NULL
GROUP BY person.id, validity_type.id
ORDER BY pilot, validity_name</sql>

=Users without up to date subscription=
Following extra field required:
*validityTypeId (Type: dbObject:ValidityType)
*year (Type: Year)

<sql>SELECT
last_name AS Nom,
first_name AS prénom,
email,
home_phone AS tel_dommicile,
work_phone AS tel_travail,
cell_phone AS tel_mobile,
DATE_FORMAT(validity.expire_date,'%d/%m/%Y') AS date_cotisation
FROM person
LEFT JOIN validity ON (validity.person_id = person.id)
LEFT JOIN validity_type ON (validity_type.id = validity.validity_type_id)
WHERE person.activated=1 AND YEAR(validity.expire_date) < $year AND validity_type.id = '$validityTypeId'
ORDER BY last_name,first_name</sql>

=Users without an account=
<SQL>SELECT person.last_name, person.first_name FROM person
LEFT JOIN account ON account.owner_id=person.id
LEFT JOIN account_type ON account.account_type=account_type.id
WHERE person.activated=1 AND account.category=2 AND account_type.activated=1 AND account.activated=0</SQL>

=Young users=
<SQL>SELECT
last_name AS Last_name,
first_name AS First_name,
DATE_FORMAT(birthdate ,'%m-%d-%Y') AS Birthdate,
IF ( (sex = 0), 'Male', 'Female' ) AS sex
FROM person
WHERE ($year-YEAR(birthdate))<=21</SQL>