ZsQb4xSu

1-- phpMyAdmin SQL Dump
2-- version 3.4.5
3-- http://www.phpmyadmin.net
4--
5-- Client: 127.0.0.1
6-- Généré le : Sam 31 Août 2013 à 15:40
7-- Version du serveur: 5.5.16
8-- Version de PHP: 5.3.8
9
10SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
11
12/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
13/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
14/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
15/*!40101 SET NAMES utf8 */;
16
17--
18-- Base de données: `gsb_frais`
19--
20
21-- --------------------------------------------------------
22
23--
24-- Structure de la table `fraisforfait`
25--
26
27CREATE TABLE IF NOT EXISTS `fraisforfait` (
28  `id` char(3) NOT NULL,
29  `libelle` char(20) DEFAULT NULL,
30  `montant` decimal(5,2) DEFAULT NULL,
31  PRIMARY KEY (`id`)
32) ENGINE=InnoDB DEFAULT CHARSET=utf8;
33
34-- --------------------------------------------------------
35
36--
37-- Structure de la table `etat`
38--
39
40CREATE TABLE IF NOT EXISTS `etat` (
41  `id` char(2) NOT NULL,
42  `libelle` varchar(30) DEFAULT NULL,
43  PRIMARY KEY (`id`)
44) ENGINE=InnoDB DEFAULT CHARSET=utf8;
45
46-- --------------------------------------------------------
47
48--
49-- Structure de la table `typevisiteur`
50--
51
52CREATE TABLE IF NOT EXISTS `typevisiteur` (
53  `idType` char(1) NOT NULL,
54  `libelleType` varchar(20) DEFAULT NULL,
55  PRIMARY KEY (`idType`)
56) ENGINE=InnoDB DEFAULT CHARSET=utf8;
57
58-- --------------------------------------------------------
59
60--
61-- Structure de la table `visiteur`
62--
63
64CREATE TABLE IF NOT EXISTS `visiteur` (
65  `id` char(4) NOT NULL,
66  `nom` char(30) DEFAULT NULL,
67  `prenom` char(30) DEFAULT NULL,
68  `login` char(20) DEFAULT NULL,
69  `mdp` char(20) DEFAULT NULL,
70  `adresse` char(30) DEFAULT NULL,
71  `cp` char(5) DEFAULT NULL,
72  `ville` char(30) DEFAULT NULL,
73  `dateEmbauche` date DEFAULT NULL,
74  `typeVisiteur` char(1) DEFAULT NULL,
75  PRIMARY KEY (`id`),
76  FOREIGN KEY (`typeVisiteur`) REFERENCES typevisiteur(`idType`)
77) ENGINE=InnoDB DEFAULT CHARSET=utf8;
78
79-- --------------------------------------------------------
80
81--
82-- Structure de la table `fichefrais`
83--
84
85CREATE TABLE IF NOT EXISTS `fichefrais` (
86  `idVisiteur` char(4) NOT NULL,
87  `mois` char(6) NOT NULL,
88  `nbJustificatifs` int(11) DEFAULT NULL,
89  `montantValide` decimal(10,2) DEFAULT NULL,
90  `dateModif` date DEFAULT NULL,
91  `idEtat` char(2) DEFAULT 'CR',
92  PRIMARY KEY (`idVisiteur`,`mois`),
93  FOREIGN KEY (`idEtat`) REFERENCES Etat(`id`),
94  FOREIGN KEY (`idVisiteur`) REFERENCES Visiteur(`id`)
95) ENGINE=InnoDB DEFAULT CHARSET=utf8;
96
97-- --------------------------------------------------------
98
99--
100-- Structure de la table `lignefraisforfait`
101--
102
103CREATE TABLE IF NOT EXISTS `lignefraisforfait` (
104  `idVisiteur` char(4) NOT NULL,
105  `mois` char(6) NOT NULL,
106  `idFraisForfait` char(3) NOT NULL,
107  `quantite` int(11) DEFAULT NULL,
108  `montantApplique` decimal(5,2) NOT NULL,
109  PRIMARY KEY (`idVisiteur`,`mois`,`idFraisForfait`),
110  FOREIGN KEY (`idVisiteur`, `mois`) REFERENCES FicheFrais(`idVisiteur`, `mois`),
111  FOREIGN KEY (`idFraisForfait`) REFERENCES FraisForfait(`id`)
112) ENGINE=InnoDB DEFAULT CHARSET=utf8;
113
114-- --------------------------------------------------------
115
116--
117-- Structure de la table `lignefraishorsforfait`
118--
119
120CREATE TABLE IF NOT EXISTS `lignefraishorsforfait` (
121  `id` int(11) NOT NULL AUTO_INCREMENT,
122  `idVisiteur` char(4) NOT NULL,
123  `mois` char(6) NOT NULL,
124  `libelle` varchar(100) DEFAULT NULL,
125  `date` date DEFAULT NULL,
126  `montant` decimal(10,2) DEFAULT NULL,
127  PRIMARY KEY (`id`),
128  FOREIGN KEY (`idVisiteur`, `mois`) REFERENCES FicheFrais(`idVisiteur`, `mois`)
129) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
130
131/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
132/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
133/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;