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