# SQL Manager 2005 for MySQL 3.7.5.1
# ---------------------------------------
# Host     : localhost
# Port     : 3306
# Database : pafauf_bdd


DROP DATABASE IF EXISTS `pafauf_bdd`;

CREATE DATABASE `pafauf_bdd`
    CHARACTER SET 'utf8'
    COLLATE 'utf8_general_ci';

USE `pafauf_bdd`;

#
# Structure for the `tblaccountaccess` table : 
#

DROP TABLE IF EXISTS `tblaccountaccess`;

CREATE TABLE `tblaccountaccess` (
  `ID` int(15) NOT NULL AUTO_INCREMENT,
  `username` varchar(300) DEFAULT NULL,
  `accesstime` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1;

#
# Structure for the `tbldomaine` table : 
#

DROP TABLE IF EXISTS `tbldomaine`;

CREATE TABLE `tbldomaine` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(500) NOT NULL,
  `other` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;

#
# Structure for the `tblinscrit` table : 
#

DROP TABLE IF EXISTS `tblinscrit`;

CREATE TABLE `tblinscrit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(200) NOT NULL,
  `prenom` varchar(200) NOT NULL,
  `sexe` varchar(1) NOT NULL,
  `datedenaissance` date NOT NULL DEFAULT '0000-00-00',
  `nationalite` varchar(100) DEFAULT NULL,
  `login` varchar(1) NOT NULL,
  `motdepasse` varchar(300) DEFAULT NULL,
  `universite` varchar(300) DEFAULT NULL,
  `faculte` varchar(300) DEFAULT NULL,
  `promotion` int(11) DEFAULT NULL,
  `anneediplome` int(11) DEFAULT NULL,
  `delf` varchar(20) DEFAULT NULL,
  `anneedelf` int(11) DEFAULT NULL,
  `langueautre` varchar(1000) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `telephonepersonnelle` varchar(20) DEFAULT NULL,
  `adressepersonnelle` text,
  `telephoneparents` varchar(20) DEFAULT NULL,
  `adresseparents` text,
  `titretravail` varchar(1000) DEFAULT NULL,
  `adressetravail` text,
  `divers` text,
  `dateinlogin` date DEFAULT NULL,
  `datein` date DEFAULT NULL,
  `supprimer` int(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

#
# Structure for the `tblnationalite` table : 
#

DROP TABLE IF EXISTS `tblnationalite`;

CREATE TABLE `tblnationalite` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(500) NOT NULL,
  `other` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

#
# Structure for the `tbluniversite` table : 
#

DROP TABLE IF EXISTS `tbluniversite`;

CREATE TABLE `tbluniversite` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(500) DEFAULT NULL,
  `other` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

#
# Definition for the `v_excel` view : 
#

DROP VIEW IF EXISTS `v_excel`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_excel` AS 
  select 
    `tblinscrit`.`id` AS `Numéro`,
    `tblinscrit`.`nom` AS `Nom`,
    `tblinscrit`.`prenom` AS `Prénom`,
    `tblinscrit`.`sexe` AS `Genre`,
    `tblinscrit`.`datedenaissance` AS `Date de Naissance`,
    `tblinscrit`.`nationalite` AS `Nationalité`,
    `tblinscrit`.`universite` AS `Université`,
    `tblinscrit`.`faculte` AS `Faculté`,
    `tblinscrit`.`promotion` AS `Promotion`,
    `tblinscrit`.`anneediplome` AS `Année Obtention de Diplôme`,
    `tblinscrit`.`delf` AS `DELF / DALF`,
    `tblinscrit`.`anneedelf` AS `Année Obtention DELF / DALF`,
    `tblinscrit`.`langueautre` AS `Autres Langues`,
    `tblinscrit`.`email` AS `E-mail`,
    `tblinscrit`.`telephonepersonnelle` AS `Téléphone Personnel`,
    `tblinscrit`.`adressepersonnelle` AS `Adresse Personnelle`,
    `tblinscrit`.`telephoneparents` AS `Téléphone de Parents`,
    `tblinscrit`.`adresseparents` AS `Adresse de Parents`,
    `tblinscrit`.`titretravail` AS `Titre de Travail ou Stage`,
    `tblinscrit`.`adressetravail` AS `Adresse de Travail ou Stage Actuel` 
  from 
    `tblinscrit`;

#
# Data for the `tblaccountaccess` table  (LIMIT 0,500)
#

INSERT INTO `tblaccountaccess` (`ID`, `username`, `accesstime`) VALUES 
  (1,'C1','2011-01-15 11:09:32'),
  (2,'C1','2011-01-15 17:10:10'),
  (3,'C1','2011-01-15 21:12:24'),
  (4,'C3','2011-01-15 22:05:24'),
  (5,'C2','2011-01-15 22:05:58'),
  (6,'C1','2011-01-15 23:29:42'),
  (7,'C1','2011-01-17 20:38:46'),
  (8,'C1','2011-01-17 23:12:04'),
  (9,'C1','2011-01-17 23:15:38'),
  (10,'C1','2011-01-17 23:45:36'),
  (11,'C1','2011-01-17 23:46:33'),
  (12,'C1','2011-01-17 23:57:33'),
  (13,'C1','2011-01-18 00:06:57'),
  (14,'C1','2011-01-18 21:43:54'),
  (15,'C3','2011-01-18 22:09:37'),
  (16,'C4','2011-01-18 22:12:15'),
  (17,'C5','2011-01-18 22:50:17'),
  (18,'C1','2011-01-25 21:17:41'),
  (19,'C1','2011-01-25 21:32:00'),
  (20,'C1','2011-01-25 23:51:31'),
  (21,'C1','2011-01-26 00:25:01'),
  (22,'C1','2011-01-26 00:28:20'),
  (23,'c1','2011-04-29 22:14:33'),
  (24,'c1','2011-04-29 22:15:14'),
  (25,'c1','2011-05-01 21:10:18'),
  (26,'c1','2011-05-01 21:50:34'),
  (27,'c1','2011-05-01 21:50:48'),
  (28,'c1','2011-05-04 21:22:38'),
  (29,'c1','2011-05-04 21:30:58'),
  (30,'c6','2011-05-04 22:29:03'),
  (31,'c7','2011-05-04 23:14:15'),
  (32,'c8','2011-05-04 23:18:22'),
  (33,'c6','2011-05-04 23:48:32'),
  (34,'c7','2011-05-04 23:49:02');

COMMIT;

#
# Data for the `tbldomaine` table  (LIMIT 0,500)
#

INSERT INTO `tbldomaine` (`id`, `nom`, `other`) VALUES 
  (1,'Agronomie',NULL),
  (2,'Anglais',NULL),
  (3,'Archéologie',NULL),
  (4,'Architecture',NULL),
  (5,'Biologie',NULL),
  (6,'Chimie',NULL),
  (7,'Commerce',NULL),
  (8,'Droit',NULL),
  (9,'Français',NULL),
  (10,'Génie Civil',NULL),
  (11,'Génie Electrique',NULL),
  (12,'Génie Industriel',NULL),
  (13,'Géographie',NULL),
  (14,'Géologie',NULL),
  (15,'Gestion',NULL),
  (16,'Histoire',NULL),
  (17,'Hydraulique',NULL),
  (18,'Informatique',NULL),
  (19,'Ingénieur',NULL),
  (20,'Littérature',NULL),
  (21,'Linguistique',NULL),
  (22,'Mathématique',NULL),
  (23,'Médecine',NULL),
  (24,'Odontostomatologie',NULL),
  (25,'Pharmacie',NULL),
  (26,'Philosophie',NULL),
  (27,'Technicien',NULL),
  (28,'Tourisme',NULL),
  (29,'Autre',NULL);

COMMIT;

#
# Data for the `tblinscrit` table  (LIMIT 0,500)
#

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 
  (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),
  (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),
  (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),
  (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),
  (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),
  (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),
  (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),
  (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);

COMMIT;

#
# Data for the `tblnationalite` table  (LIMIT 0,500)
#

INSERT INTO `tblnationalite` (`id`, `nom`, `other`) VALUES 
  (1,'Cambodgienne',NULL),
  (2,'Belge',NULL),
  (3,'Canadienne',NULL),
  (4,'Chinoise',NULL),
  (5,'Française',NULL),
  (6,'Laotienne',NULL),
  (7,'Suisse',NULL),
  (8,'Vietnamienne',NULL),
  (9,'Malagasy',NULL),
  (10,'Autre',NULL);

COMMIT;

#
# Data for the `tbluniversite` table  (LIMIT 0,500)
#

INSERT INTO `tbluniversite` (`id`, `nom`, `other`) VALUES 
  (1,'AUF',NULL),
  (2,'Ecole Royale Administration',NULL),
  (3,'Faculty of Business',NULL),
  (4,'ITC',NULL),
  (5,'Lycée',NULL),
  (6,'Univ. Royale de Phnom Penh (URPP)',NULL),
  (7,'Univ. Royale des Beaux-Arts (URBA)',NULL),
  (8,'Univ. Royale Agronomie (URA)',NULL),
  (9,'Univ. des Sciences de la Santé (USS)',NULL),
  (10,'Univ. de Droit et Science-Eco (URDSE)',NULL),
  (11,'Autre',NULL);

COMMIT;