#
# TABLE STRUCTURE FOR: reports
#

DROP TABLE IF EXISTS reports;

CREATE TABLE `reports` (
  `nom` varchar(64) NOT NULL COMMENT 'Nom du rapport',
  `titre` varchar(64) NOT NULL COMMENT 'Titre du rapport',
  `fields_list` varchar(128) NOT NULL,
  `align` varchar(128) NOT NULL COMMENT 'Alignement des colonnes',
  `width` varchar(128) NOT NULL,
  `landscape` tinyint(4) NOT NULL,
  `sql` varchar(2048) NOT NULL COMMENT 'Requête sql',
  PRIMARY KEY  (`nom`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Rapports définis par l''utilisateur';

INSERT INTO reports (`nom`, `titre`, `fields_list`, `align`, `width`, `landscape`, `sql`) VALUES ('utilisateurs', 'Liste des utilisateurs', 'Nom,Prénom,+/-25,Naissance', 'left,left,right,right', '', 0, 'select mnom, mprenom, m25ans, date_format(mdaten, \"%d/%m/%Y\") from membres;');
INSERT INTO reports (`nom`, `titre`, `fields_list`, `align`, `width`, `landscape`, `sql`) VALUES ('tarifs', 'Liste des tarifs', 'Référence,Description,Date,Prix,Compte', 'left,left,right,right,left', '32,32,32,32,32', 0, 'select reference, description, date_format(date, \"%d/%m/%Y\"), prix, comptes.nom  from tarifs, comptes where tarifs.compte=comptes.id order by reference,date;');
INSERT INTO reports (`nom`, `titre`, `fields_list`, `align`, `width`, `landscape`, `sql`) VALUES ('tarifs_clone', 'Liste des tarifs', 'Référence,Description,Date,Prix,Compte', 'left,left,right,right,left', '32,32,32,32,32', 0, 'select b.reference, b.description, date_format (b.date, \"%d/%m/%Y\"), b.prix, comptes.nom\nfrom tarifs b,comptes, (\n  select reference, date, max(date) as ladate \n  from tarifs\n  where date < \'2012-12-31\'\n  group by reference\n  ) a\nwhere b.date = a.ladate\nand b.compte = comptes.id\nand b.reference = a.reference\norder by b.reference');


#
# TABLE STRUCTURE FOR: type_ticket
#

DROP TABLE IF EXISTS type_ticket;

CREATE TABLE `type_ticket` (
  `id` int(11) NOT NULL COMMENT 'Identifiant',
  `nom` varchar(64) NOT NULL COMMENT 'Nom',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Type de tickets';

#
# TABLE STRUCTURE FOR: terrains
#

DROP TABLE IF EXISTS terrains;

CREATE TABLE `terrains` (
  `oaci` varchar(10) collate utf8_bin NOT NULL COMMENT 'Code OACI',
  `nom` varchar(64) collate utf8_bin default NULL COMMENT 'Nom du terrain',
  `freq1` decimal(6,3) default NULL COMMENT 'Fréquence principale',
  `freq2` decimal(6,3) default NULL COMMENT 'Fréquence secondaire',
  `comment` varchar(256) collate utf8_bin default NULL COMMENT 'Description',
  PRIMARY KEY  (`oaci`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO terrains (`oaci`, `nom`, `freq1`, `freq2`, `comment`) VALUES ('LFOI', 'Abbeville', '123.500', '0.000', '');
INSERT INTO terrains (`oaci`, `nom`, `freq1`, `freq2`, `comment`) VALUES ('LFAY', 'Amiens Glisy', '123.400', '0.000', '');
INSERT INTO terrains (`oaci`, `nom`, `freq1`, `freq2`, `comment`) VALUES ('LFQB', 'Troyes - Barberey', '123.725', '0.000', '');
INSERT INTO terrains (`oaci`, `nom`, `freq1`, `freq2`, `comment`) VALUES ('LFQO', 'Lille - Marq en Bareuil', '0.000', '0.000', '');


#
# TABLE STRUCTURE FOR: events_types
#

DROP TABLE IF EXISTS events_types;

CREATE TABLE `events_types` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(32) collate utf8_bin NOT NULL,
  `activite` tinyint(2) NOT NULL default '0' COMMENT '0=Autre,1=planeur,2=avion,3=ULM,4=FAI',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO events_types (`id`, `name`, `activite`) VALUES (14, 'Premier vol', 1);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (15, 'Laché planeur', 1);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (16, 'Vol 1h', 1);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (17, 'Vol 5h', 4);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (18, 'Gain de 1000m', 4);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (19, 'Gain de 3000m', 4);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (20, 'Gain de 5000m', 4);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (21, 'Distance de 50km', 4);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (22, 'Distance de 300km', 4);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (23, 'Distance de 500km', 4);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (24, 'Distance de 750km', 4);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (25, 'Distance de 1000km', 4);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (26, 'Visite médical', 0);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (27, 'BPP', 1);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (28, 'BIA', 0);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (29, 'Campagne', 1);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (30, 'Contôle de compétence', 1);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (31, 'Circuit de 300km FAI', 4);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (33, 'Théorique BPP', 1);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (34, 'Emport passager', 1);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (35, 'Laché avion', 2);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (36, 'BB', 2);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (37, 'PPL', 2);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (38, 'Validité licence avion', 2);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (39, 'FI Formateur instructeur', 2);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (40, 'FE Formateur examinateur', 2);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (41, 'Autorisation remorquage', 2);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (42, 'Premier vol avion', 1);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (43, 'ITP', 1);
INSERT INTO events_types (`id`, `name`, `activite`) VALUES (44, 'ITV', 1);


#
# TABLE STRUCTURE FOR: planc
#

DROP TABLE IF EXISTS planc;

CREATE TABLE `planc` (
  `pcode` varchar(10) NOT NULL,
  `pdesc` varchar(50) NOT NULL,
  UNIQUE KEY `pcode` (`pcode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO planc (`pcode`, `pdesc`) VALUES ('101', 'Capital');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('215', 'Matériel');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('218', 'Mobilier.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('371', 'Marchandises');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('401', 'Fournisseurs');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('411', 'Clients');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('512', 'Banque');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('531', 'Caisse');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('60', 'Achats');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('601', 'Achats stockés - Matières premières et fournitures');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('602', 'Achats stockés - Autres approvisionements');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('606', 'Achats non stockés de matières et fournitures');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('604', 'Achats d\'études et prestations de services');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('605', 'Achat autres.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('61', 'Services extérieurs');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('611', 'Sous-traitance générale');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('612', 'Redevances de crédit-bail');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('613', 'Locations');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('615', 'Entretien et réparations');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('616', 'Assurances');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('62', 'Autres services extérieurs');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('621', 'Personels extérieur à l\'association');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('622', 'Rémunérations et Honoraires.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('623', 'Publicité, Publications.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('624', 'Transport de bien et transport collectif du person');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('625', 'Déplacement, missions et reception');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('626', 'Frais postaux et télécommunications');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('627', 'Licences, Cotisations Fédérales FFVV.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('628', 'Divers, cotisations');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('629', 'Rabais, ristournes, remises sur services extérieur');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('63', 'Impôts et Taxes');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('631', 'Impots sur rémunération');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('635', 'Autres impôts et Taxes.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('64', 'Charges de Personnel');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('65', 'Autres Charges de gestion courante');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('651', 'Redevance pour concessions, brevets');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('66', 'Charges financières');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('67', 'Chages Exceptionnelles');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('674', 'Autres.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('68', 'Dotation aux Amortissements');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('70', 'Ventes');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('701', 'Vente d\'essence.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('702', 'Vols d\'initiation planeur');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('703', 'Vols d\'initiation Avion');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('704', 'Recettes autres manifestations');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('705', 'Sponsoring');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('74', 'Subventions d\'exploitation');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('741', 'Etat.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('742', 'Région.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('743', 'Département.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('744', 'communes.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('745', 'CCSRC (communauté de commune)');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('746', 'Autres subventions.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('75', 'Autres produits de gestion courante');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('751', 'Cotisations membres');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('752', 'Assurances licences FFA.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('753', 'Assurances licences FFVV.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('754', 'Retour des Fédérations (bourses).');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('755', 'tickets de remorquage des planeurs.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('756', 'Heures planeurs.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('757', 'Heures avion.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('758', 'Compensation sur tickets Argenton.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('76', 'Produits financiers');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('77', 'Produits exceptionnels');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('771', 'Produits exceptionels');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('772', 'ANEPVV des privés.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('773', 'Argenton (indem. d\'hébergement)');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('774', 'Autres.');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('78', 'Reprise sur amortissements');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('110', 'Report à nouveau (solde créditeur)');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('119', 'Report à nouveau (solde débiteur)');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('441', 'Etat - Subventions');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('778', 'Autres produits exceptionnels');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('678', 'Autres charges exceptionnelles');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('654', 'Redistribution des Bourses ou Subventions');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('487', 'Produits constatés d\'avance');
INSERT INTO planc (`pcode`, `pdesc`) VALUES ('46', 'Débiteurs divers et créditeur divers');


#
# TABLE STRUCTURE FOR: roles
#

DROP TABLE IF EXISTS roles;

CREATE TABLE `roles` (
  `id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) NOT NULL default '0',
  `name` varchar(30) collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO roles (`id`, `parent_id`, `name`) VALUES (9, 3, 'tresorier');
INSERT INTO roles (`id`, `parent_id`, `name`) VALUES (8, 7, 'ca');
INSERT INTO roles (`id`, `parent_id`, `name`) VALUES (7, 1, 'planchiste');
INSERT INTO roles (`id`, `parent_id`, `name`) VALUES (2, 9, 'admin');
INSERT INTO roles (`id`, `parent_id`, `name`) VALUES (1, 0, 'membre');
INSERT INTO roles (`id`, `parent_id`, `name`) VALUES (3, 8, 'bureau');


