Première version publique
[auf_paf.git] / pafauf_bdd.sql
1 # SQL Manager 2005 for MySQL 3.7.5.1
2 # ---------------------------------------
3 # Host : localhost
4 # Port : 3306
5 # Database : pafauf_bdd
6
7
8 DROP DATABASE IF EXISTS `pafauf_bdd`;
9
10 CREATE DATABASE `pafauf_bdd`
11 CHARACTER SET 'utf8'
12 COLLATE 'utf8_general_ci';
13
14 USE `pafauf_bdd`;
15
16 #
17 # Structure for the `tblaccountaccess` table :
18 #
19
20 DROP TABLE IF EXISTS `tblaccountaccess`;
21
22 CREATE TABLE `tblaccountaccess` (
23 `ID` int(15) NOT NULL AUTO_INCREMENT,
24 `username` varchar(300) DEFAULT NULL,
25 `accesstime` datetime DEFAULT NULL,
26 PRIMARY KEY (`ID`)
27 ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1;
28
29 #
30 # Structure for the `tbldomaine` table :
31 #
32
33 DROP TABLE IF EXISTS `tbldomaine`;
34
35 CREATE TABLE `tbldomaine` (
36 `id` int(11) NOT NULL AUTO_INCREMENT,
37 `nom` varchar(500) NOT NULL,
38 `other` varchar(500) DEFAULT NULL,
39 PRIMARY KEY (`id`)
40 ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
41
42 #
43 # Structure for the `tblinscrit` table :
44 #
45
46 DROP TABLE IF EXISTS `tblinscrit`;
47
48 CREATE TABLE `tblinscrit` (
49 `id` int(11) NOT NULL AUTO_INCREMENT,
50 `nom` varchar(200) NOT NULL,
51 `prenom` varchar(200) NOT NULL,
52 `sexe` varchar(1) NOT NULL,
53 `datedenaissance` date NOT NULL DEFAULT '0000-00-00',
54 `nationalite` varchar(100) DEFAULT NULL,
55 `login` varchar(1) NOT NULL,
56 `motdepasse` varchar(300) DEFAULT NULL,
57 `universite` varchar(300) DEFAULT NULL,
58 `faculte` varchar(300) DEFAULT NULL,
59 `promotion` int(11) DEFAULT NULL,
60 `anneediplome` int(11) DEFAULT NULL,
61 `delf` varchar(20) DEFAULT NULL,
62 `anneedelf` int(11) DEFAULT NULL,
63 `langueautre` varchar(1000) DEFAULT NULL,
64 `email` varchar(100) DEFAULT NULL,
65 `telephonepersonnelle` varchar(20) DEFAULT NULL,
66 `adressepersonnelle` text,
67 `telephoneparents` varchar(20) DEFAULT NULL,
68 `adresseparents` text,
69 `titretravail` varchar(1000) DEFAULT NULL,
70 `adressetravail` text,
71 `divers` text,
72 `dateinlogin` date DEFAULT NULL,
73 `datein` date DEFAULT NULL,
74 `supprimer` int(1) DEFAULT '0',
75 PRIMARY KEY (`id`)
76 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
77
78 #
79 # Structure for the `tblnationalite` table :
80 #
81
82 DROP TABLE IF EXISTS `tblnationalite`;
83
84 CREATE TABLE `tblnationalite` (
85 `id` int(11) NOT NULL AUTO_INCREMENT,
86 `nom` varchar(500) NOT NULL,
87 `other` varchar(500) DEFAULT NULL,
88 PRIMARY KEY (`id`)
89 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
90
91 #
92 # Structure for the `tbluniversite` table :
93 #
94
95 DROP TABLE IF EXISTS `tbluniversite`;
96
97 CREATE TABLE `tbluniversite` (
98 `id` int(11) NOT NULL AUTO_INCREMENT,
99 `nom` varchar(500) DEFAULT NULL,
100 `other` varchar(500) DEFAULT NULL,
101 PRIMARY KEY (`id`)
102 ) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
103
104 #
105 # Definition for the `v_excel` view :
106 #
107
108 DROP VIEW IF EXISTS `v_excel`;
109
110 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_excel` AS
111 select
112 `tblinscrit`.`id` AS `Numéro`,
113 `tblinscrit`.`nom` AS `Nom`,
114 `tblinscrit`.`prenom` AS `Prénom`,
115 `tblinscrit`.`sexe` AS `Genre`,
116 `tblinscrit`.`datedenaissance` AS `Date de Naissance`,
117 `tblinscrit`.`nationalite` AS `Nationalité`,
118 `tblinscrit`.`universite` AS `Université`,
119 `tblinscrit`.`faculte` AS `Faculté`,
120 `tblinscrit`.`promotion` AS `Promotion`,
121 `tblinscrit`.`anneediplome` AS `Année Obtention de Diplôme`,
122 `tblinscrit`.`delf` AS `DELF / DALF`,
123 `tblinscrit`.`anneedelf` AS `Année Obtention DELF / DALF`,
124 `tblinscrit`.`langueautre` AS `Autres Langues`,
125 `tblinscrit`.`email` AS `E-mail`,
126 `tblinscrit`.`telephonepersonnelle` AS `Téléphone Personnel`,
127 `tblinscrit`.`adressepersonnelle` AS `Adresse Personnelle`,
128 `tblinscrit`.`telephoneparents` AS `Téléphone de Parents`,
129 `tblinscrit`.`adresseparents` AS `Adresse de Parents`,
130 `tblinscrit`.`titretravail` AS `Titre de Travail ou Stage`,
131 `tblinscrit`.`adressetravail` AS `Adresse de Travail ou Stage Actuel`
132 from
133 `tblinscrit`;
134
135 #
136 # Data for the `tblaccountaccess` table (LIMIT 0,500)
137 #
138
139 INSERT INTO `tblaccountaccess` (`ID`, `username`, `accesstime`) VALUES
140 (1,'C1','2011-01-15 11:09:32'),
141 (2,'C1','2011-01-15 17:10:10'),
142 (3,'C1','2011-01-15 21:12:24'),
143 (4,'C3','2011-01-15 22:05:24'),
144 (5,'C2','2011-01-15 22:05:58'),
145 (6,'C1','2011-01-15 23:29:42'),
146 (7,'C1','2011-01-17 20:38:46'),
147 (8,'C1','2011-01-17 23:12:04'),
148 (9,'C1','2011-01-17 23:15:38'),
149 (10,'C1','2011-01-17 23:45:36'),
150 (11,'C1','2011-01-17 23:46:33'),
151 (12,'C1','2011-01-17 23:57:33'),
152 (13,'C1','2011-01-18 00:06:57'),
153 (14,'C1','2011-01-18 21:43:54'),
154 (15,'C3','2011-01-18 22:09:37'),
155 (16,'C4','2011-01-18 22:12:15'),
156 (17,'C5','2011-01-18 22:50:17'),
157 (18,'C1','2011-01-25 21:17:41'),
158 (19,'C1','2011-01-25 21:32:00'),
159 (20,'C1','2011-01-25 23:51:31'),
160 (21,'C1','2011-01-26 00:25:01'),
161 (22,'C1','2011-01-26 00:28:20'),
162 (23,'c1','2011-04-29 22:14:33'),
163 (24,'c1','2011-04-29 22:15:14'),
164 (25,'c1','2011-05-01 21:10:18'),
165 (26,'c1','2011-05-01 21:50:34'),
166 (27,'c1','2011-05-01 21:50:48'),
167 (28,'c1','2011-05-04 21:22:38'),
168 (29,'c1','2011-05-04 21:30:58'),
169 (30,'c6','2011-05-04 22:29:03'),
170 (31,'c7','2011-05-04 23:14:15'),
171 (32,'c8','2011-05-04 23:18:22'),
172 (33,'c6','2011-05-04 23:48:32'),
173 (34,'c7','2011-05-04 23:49:02');
174
175 COMMIT;
176
177 #
178 # Data for the `tbldomaine` table (LIMIT 0,500)
179 #
180
181 INSERT INTO `tbldomaine` (`id`, `nom`, `other`) VALUES
182 (1,'Agronomie',NULL),
183 (2,'Anglais',NULL),
184 (3,'Archéologie',NULL),
185 (4,'Architecture',NULL),
186 (5,'Biologie',NULL),
187 (6,'Chimie',NULL),
188 (7,'Commerce',NULL),
189 (8,'Droit',NULL),
190 (9,'Français',NULL),
191 (10,'Génie Civil',NULL),
192 (11,'Génie Electrique',NULL),
193 (12,'Génie Industriel',NULL),
194 (13,'Géographie',NULL),
195 (14,'Géologie',NULL),
196 (15,'Gestion',NULL),
197 (16,'Histoire',NULL),
198 (17,'Hydraulique',NULL),
199 (18,'Informatique',NULL),
200 (19,'Ingénieur',NULL),
201 (20,'Littérature',NULL),
202 (21,'Linguistique',NULL),
203 (22,'Mathématique',NULL),
204 (23,'Médecine',NULL),
205 (24,'Odontostomatologie',NULL),
206 (25,'Pharmacie',NULL),
207 (26,'Philosophie',NULL),
208 (27,'Technicien',NULL),
209 (28,'Tourisme',NULL),
210 (29,'Autre',NULL);
211
212 COMMIT;
213
214 #
215 # Data for the `tblinscrit` table (LIMIT 0,500)
216 #
217
218 INSERT INTO `tblinscrit` (`id`, `nom`, `prenom`, `sexe`, `datedenaissance`, `nationalite`, `login`, `motdepasse`, `universite`, `faculte`, `promotion`, `anneediplome`, `delf`, `anneedelf`, `langueautre`, `email`, `telephonepersonnelle`, `adressepersonnelle`, `telephoneparents`, `adresseparents`, `titretravail`, `adressetravail`, `divers`, `dateinlogin`, `datein`, `supprimer`) VALUES
219 (1,'TIV','Chhunly','M','1980-03-07','Laotienne','C','4OONmNDO1w==','??','??',1950,1950,'DALF',1954,'Anglais','tclyit@gmail.com','012343854899 ? ?','PP`s','0123854358','SRédiction','Responsable de Formation au CNF','ITC','Bopha','2011-01-12','2011-05-04',0),
220 (2,'Ong','Bopha','F','1981-12-02','Cambodgien','C','4OONmNDO1w==','Univ. Royale de Phnom Penh','Linguistique',2009,2009,'DALF',2007,'Anglais','ong_bopha@yahoo.com','01245545577','Battambang','012457788','Battambang','PhD','Canada','Toronto','2011-01-12','2011-01-15',0),
221 (3,'Ong','Bopha','F','2011-02-02','Cambodgien','C','3+qVi8M=','ITC','Génie Civil',1950,1948,'DALF',1950,'Anglais','ong_bopha@yahoo.com','012343854899','PP','012457788','BB','PhD','Toronto','Chhunly','2011-01-13','2011-01-18',0),
222 (4,'TIV','Poteang','F','2001-02-01','Cambodgien','C','rq1YV5eY','Univ. des Sciences de la Santé','Ingénieur',2003,2007,'DALF',1978,'Anglais','ong_bopha@yahoo.com','012343854899','SR','012457788','PP','PhD','Manager','company','2011-01-18','2011-01-18',0),
223 (5,'TIV','Potin','F','2003-06-05','Cambodgien','C','rq1YV5eY','Univ. Royale de Phnom Penh','Commerce',1987,2004,'DALF',1994,'Anglais','tivpotin@yahoo.com','012343854899','','','','','','','2011-01-18','2011-01-18',0),
224 (6,'Tong','Vong','M','1980-01-01','Française','C','rq1Y','Ecole Royale d','Littérature',0,0,'',0,'','','','','','','','','','2011-05-04','2011-05-04',0),
225 (7,'Voila','Pos','F','1980-02-02','Chinoise','C','rq1Y','Univ. des Sciences de la Santé (USS)','Génie Industriel',0,0,'',0,'','','','','','','','','','2011-05-04','2011-05-04',0),
226 (8,'Pily','Site','F','1980-03-03','Suisse','C','rq1Y','Lycée','Génie Civil',1986,0,'',0,'','','','','','','','','','2011-05-04','2011-05-04',0);
227
228 COMMIT;
229
230 #
231 # Data for the `tblnationalite` table (LIMIT 0,500)
232 #
233
234 INSERT INTO `tblnationalite` (`id`, `nom`, `other`) VALUES
235 (1,'Cambodgienne',NULL),
236 (2,'Belge',NULL),
237 (3,'Canadienne',NULL),
238 (4,'Chinoise',NULL),
239 (5,'Française',NULL),
240 (6,'Laotienne',NULL),
241 (7,'Suisse',NULL),
242 (8,'Vietnamienne',NULL),
243 (9,'Malagasy',NULL),
244 (10,'Autre',NULL);
245
246 COMMIT;
247
248 #
249 # Data for the `tbluniversite` table (LIMIT 0,500)
250 #
251
252 INSERT INTO `tbluniversite` (`id`, `nom`, `other`) VALUES
253 (1,'AUF',NULL),
254 (2,'Ecole Royale Administration',NULL),
255 (3,'Faculty of Business',NULL),
256 (4,'ITC',NULL),
257 (5,'Lycée',NULL),
258 (6,'Univ. Royale de Phnom Penh (URPP)',NULL),
259 (7,'Univ. Royale des Beaux-Arts (URBA)',NULL),
260 (8,'Univ. Royale Agronomie (URA)',NULL),
261 (9,'Univ. des Sciences de la Santé (USS)',NULL),
262 (10,'Univ. de Droit et Science-Eco (URDSE)',NULL),
263 (11,'Autre',NULL);
264
265 COMMIT;
266