Identifier chaque utilisateur

Avoir le nom de celui qui a fait la facture

a marqué ce sujet comme résolu.

Bonjour, je fais actuellement une application web de gestion d’hôtel et je suis débutant en programmation et j’aimerais savoir :

  • comment faire pour que le nom du réceptionniste ayant servi le client apparaisse sur la facture,
  • comment faire pour éviter que le recpetionniste supprime le service, Merci d’avance
+0 -0

merci, mais je ne maitrise ps quoi écrire comme ligne de code à ce niveau, voici ma BD 

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
--
-- Base de données: `room_manager`
--


--
-- Structure de la table `customers`
--

CREATE TABLE IF NOT EXISTS `customers` (
  `ID_CUSTOMER` bigint(4) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) DEFAULT NULL,
  `SURNAME` varchar(255) DEFAULT NULL,
  `EMAIL` varchar(255) DEFAULT NULL,
  `PHONE` char(32) DEFAULT NULL,
  `CARD_TYPE` varchar(255) DEFAULT NULL,
  `CARD_NUMBER` varchar(255) DEFAULT NULL,
  `SEX` char(32) DEFAULT NULL,
  `DATE_POST` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID_CUSTOMER`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='managing users' AUTO_INCREMENT=3 ;

--
-- Contenu de la table `customers`
--

INSERT INTO `customers` (`ID_CUSTOMER`, `NAME`, `SURNAME`, `EMAIL`, `PHONE`, `CARD_TYPE`, `CARD_NUMBER`, `SEX`, `DATE_POST`) VALUES
(1, 'baby', 'doe', 'bdoe@tyjau.com', '', 'cni', '123456', 'f', '1446554218'),
(2, 'MEBA', 'Adolphe', 'mebs@madmin.com', '69465873', 'CNI', '78976546', 'm', '1448548620');

-- --------------------------------------------------------

--
-- Structure de la table `invoices`
--

CREATE TABLE IF NOT EXISTS `invoices` (
  `ID_INVOICE` bigint(4) NOT NULL AUTO_INCREMENT,
  `ID_ROOM` bigint(4) NOT NULL,
  `ID_CUSTOMER` bigint(4) NOT NULL,
  `DATE_START` char(32) DEFAULT NULL,
  `DATE_END` char(32) DEFAULT NULL,
  `NUITIES_NUMB` int(11) NOT NULL,
  `DATE_POST` char(32) DEFAULT NULL,
  PRIMARY KEY (`ID_INVOICE`),
  KEY `I_FK_INVOICES_ROOMS` (`ID_ROOM`),
  KEY `I_FK_INVOICES_CUSTOMERS` (`ID_CUSTOMER`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;

--
-- Contenu de la table `invoices`
--

INSERT INTO `invoices` (`ID_INVOICE`, `ID_ROOM`, `ID_CUSTOMER`, `DATE_START`, `DATE_END`, `NUITIES_NUMB`, `DATE_POST`) VALUES
(36, 2, 2, '2015/11/26 15:37', '2015/12/03 15:37', 7, '1448548672');

-- --------------------------------------------------------

--
-- Structure de la table `managers`
--

CREATE TABLE IF NOT EXISTS `managers` (
  `ID_MANAGER` bigint(4) NOT NULL AUTO_INCREMENT,
  `ID_ROLE` int(11) NOT NULL,
  `NAME` varchar(255) DEFAULT NULL,
  `EMAIL` varchar(255) DEFAULT NULL,
  `PHONE` char(32) DEFAULT NULL,
  `PASS` varchar(255) DEFAULT NULL,
  `STATUE` char(32) DEFAULT NULL,
  `DATE_POST` char(32) DEFAULT NULL,
  PRIMARY KEY (`ID_MANAGER`),
  KEY `ID_ROLE` (`ID_ROLE`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Contenu de la table `managers`
--

INSERT INTO `managers` (`ID_MANAGER`, `ID_ROLE`, `NAME`, `EMAIL`, `PHONE`, `PASS`, `STATUE`, `DATE_POST`) VALUES
(1, 2, 'john doe', 'john.doe@madmin.com', NULL, '527bd5b5d689e2c32ae974c6229ff785', 'on', '123464564'),
(2, 1, 'admin', 'admin@madmin.com', '', '21232f297a57a5a743894a0e4a801fc3', 'on', '1448548179');

-- --------------------------------------------------------

--
-- Structure de la table `roles`
--

CREATE TABLE IF NOT EXISTS `roles` (
  `ID_ROLE` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL,
  PRIMARY KEY (`ID_ROLE`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='managing users roles and rights' AUTO_INCREMENT=3 ;

--
-- Contenu de la table `roles`
--

INSERT INTO `roles` (`ID_ROLE`, `NAME`) VALUES
(1, 'admin'),
(2, 'user');

-- --------------------------------------------------------

--
-- Structure de la table `rooms`
--

CREATE TABLE IF NOT EXISTS `rooms` (
  `ID_ROOM` bigint(4) NOT NULL AUTO_INCREMENT,
  `ID_STANDING` bigint(4) NOT NULL,
  `NAME` char(32) DEFAULT NULL,
  `STATUE` char(32) DEFAULT NULL,
  `DATE_POST` char(32) DEFAULT NULL,
  PRIMARY KEY (`ID_ROOM`),
  KEY `I_FK_ROOMS_STANDINGS` (`ID_STANDING`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Contenu de la table `rooms`
--

INSERT INTO `rooms` (`ID_ROOM`, `ID_STANDING`, `NAME`, `STATUE`, `DATE_POST`) VALUES
(1, 1, '112', 'on', '1446554140'),
(2, 2, '112 - MEBA', 'on', '1448548577');

-- --------------------------------------------------------

--
-- Structure de la table `standings`
--

CREATE TABLE IF NOT EXISTS `standings` (
  `ID_STANDING` bigint(4) NOT NULL AUTO_INCREMENT,
  `NAME` char(32) DEFAULT NULL,
  `PRICE` char(32) DEFAULT NULL,
  `DATE_POST` char(32) DEFAULT NULL,
  PRIMARY KEY (`ID_STANDING`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Contenu de la table `standings`
--

INSERT INTO `standings` (`ID_STANDING`, `NAME`, `PRICE`, `DATE_POST`) VALUES
(1, 'junior', '15000', '1446554128'),
(2, 'junior - MEBA', '10000', '1448548537');

--
-- Contraintes pour les tables exportées
--
+0 -0

Personnellement, je renommerai la table managers pour "employees" (et donc la clé primaire "ID_MANAGER" en "ID_EMPLOYEES")

Ensuite j’ajouterai un rôle "receptionist" (vérifie la traduction :p ) dans la table roles

Enfin, j’ajouterai un clé étrangère "ID_EMPLOYEES" dans la table "invoices" Cette clé te servira à indiquer qui à effectué la réservation.

Mise en application :
  • Dans la table roles, j’ai ajouté un rôle "receptionist"
1
2
INSERT INTO `roles` (`ID_ROLE`, `NAME`) VALUES
(3, 'receptionist');
  • Ensuite, j’ai inséré un employée qui a un id 3 
1
2
INSERT INTO `employees` (`ID_EMPLOYEES`, `ID_ROLE`, `NAME`, `EMAIL`, `PHONE`, `PASS`, `STATUE`, `DATE_POST`) VALUES
(3, 3, 'clem antine', 'clem@antine.com', '', '21232f297a57a5a743894a0e4a801fc3', 'on', '1448548179');
  • J’ai ajouté une nouvelle réservation (et j’ai modifié les lignes existantes pour mettre l’id de l’employé)
    Le code d’insertion 
1
2
INSERT INTO `invoices` (`ID_ROOM`, `ID_CUSTOMER`, `ID_EMPLOYEES`, `DATE_START`, `DATE_END`, `NUITIES_NUMB`, `DATE_POST`) VALUES
(2, 2, 3, '2015/11/26 15:37', '2015/12/03 15:37', 7, '1448548672')

On peut ainsi faire une requête avec les informations de la réservation

1
2
3
4
SELECT r.name as room_name, e.name as employees_name, i.DATE_START, i.DATE_END, i.NUITIES_NUMB 
FROM rooms r
INNER JOIN invoices i USING(ID_ROOM)
INNER JOIN employees e USING(ID_EMPLOYEES)
room_name employees_name DATE_START DATE_END NUITIES_NUMB
112 - MEBA clem antine 2015/11/26 15:37 2015/12/03 15:37 7
+0 -0

Bonjour Angelo, voici ma nouvelle BD avec tes recommandations, mais rien de changer est ce une erreur de modification de ma part ?

– Base de données: room_manager

CREATE DATABASE IF NOT EXISTS room_manager DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE room_manager;

– – Structure de la table customers –

CREATE TABLE IF NOT EXISTS customers ( ID_CUSTOMER bigint(4) NOT NULL AUTO_INCREMENT, NAME varchar(255) DEFAULT NULL, SURNAME varchar(255) DEFAULT NULL, EMAIL varchar(255) DEFAULT NULL, PHONE char(32) DEFAULT NULL, CARD_TYPE varchar(255) DEFAULT NULL, CARD_NUMBER varchar(255) DEFAULT NULL, SEX char(32) DEFAULT NULL, DATE_POST varchar(255) DEFAULT NULL, PRIMARY KEY (ID_CUSTOMER) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=’managing users’ AUTO_INCREMENT=3 ;

– – Contenu de la table customers –

INSERT INTO customers (ID_CUSTOMER, NAME, SURNAME, EMAIL, PHONE, CARD_TYPE, CARD_NUMBER, SEX, DATE_POST) VALUES (1, ’baby’, ’doe’, ’bdoe@tyjau.com’, ’’, ’cni’, ’123456’, ’f’, ’1446554218’), (2, ’MEBA’, ’Adolphe’, ’mebs@madmin.com’, ’69465873’, ’CNI’, ’78976546’, ’m’, ’1448548620’);

– – Structure de la table invoices –

CREATE TABLE IF NOT EXISTS invoices ( ID_INVOICE bigint(4) NOT NULL AUTO_INCREMENT, ID_ROOM bigint(4) NOT NULL, ID_CUSTOMER bigint(4) NOT NULL, ID_EMPLOYEES bigint(4) NOT NULL, DATE_START char(32) DEFAULT NULL, DATE_END char(32) DEFAULT NULL, NUITIES_NUMB int(11) NOT NULL, DATE_POST char(32) DEFAULT NULL, PRIMARY KEY (ID_INVOICE), KEY I_FK_INVOICES_ROOMS (ID_ROOM), KEY I_FK_INVOICES_CUSTOMERS (ID_CUSTOMER) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;

– – Contenu de la table invoices –

INSERT INTO invoices ( ID_INVOICE,ID_ROOM, ID_CUSTOMER, ID_EMPLOYEES, DATE_START, DATE_END, NUITIES_NUMB, DATE_POST) VALUES (2, 2, 3, ’2015/11/26 15:37’, ’2015/12/03 15:37’, 7, ’1448548672’) (36, 2, 2, ’2015/11/26 15:37’, ’2015/12/03 15:37’, 7, ’1448548672’);

– – Structure de la table employees –

CREATE TABLE IF NOT EXISTS employees ( ID_EMPLOYEES bigint(4) NOT NULL AUTO_INCREMENT, ID_ROLE int(11) NOT NULL, NAME varchar(255) DEFAULT NULL, EMAIL varchar(255) DEFAULT NULL, PHONE char(32) DEFAULT NULL, PASS varchar(255) DEFAULT NULL, STATUE char(32) DEFAULT NULL, DATE_POST char(32) DEFAULT NULL, PRIMARY KEY (ID_EMPLOYEES), KEY ID_ROLE (ID_ROLE) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

– – Contenu de la table employees –

INSERT INTO employees (ID_EMPLOYEES, ID_ROLE, NAME, EMAIL, PHONE, PASS, STATUE, DATE_POST) VALUES (1, 3, ’john doe’, ’john.doe@madmin.com’, NULL, ’527bd5b5d689e2c32ae974c6229ff785’, ’on’, ’123464564’), (2, 3, ’admin’, ’admin@madmin.com’, ’’, ’21232f297a57a5a743894a0e4a801fc3’, ’on’, ’1448548179’); (3, 3, ’clem antine’, ’clem@antine.com’, ’’, ’21232f297a57a5a743894a0e4a801fc3’, ’on’, ’1448548179’);

– – Structure de la table roles –

CREATE TABLE IF NOT EXISTS roles ( ID_ROLE int(11) NOT NULL AUTO_INCREMENT, NAME varchar(255) NOT NULL, PRIMARY KEY (ID_ROLE) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=’managing users roles and rights’ AUTO_INCREMENT=3 ;

– – Contenu de la table roles –

INSERT INTO roles (ID_ROLE, NAME) VALUES (1, ’admin’), (2, ’user’); (3, ’book’);

– – Structure de la table rooms –

CREATE TABLE IF NOT EXISTS rooms ( ID_ROOM bigint(4) NOT NULL AUTO_INCREMENT, ID_STANDING bigint(4) NOT NULL, NAME char(32) DEFAULT NULL, STATUE char(32) DEFAULT NULL, DATE_POST char(32) DEFAULT NULL, PRIMARY KEY (ID_ROOM), KEY I_FK_ROOMS_STANDINGS (ID_STANDING) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

– – Contenu de la table rooms –

INSERT INTO rooms (ID_ROOM, ID_STANDING, NAME, STATUE, DATE_POST) VALUES (1, 1, ’112’, ’on’, ’1446554140’), (2, 2, ’112 - MEBA’, ’on’, ’1448548577’);

– – Structure de la table standings –

CREATE TABLE IF NOT EXISTS standings ( ID_STANDING bigint(4) NOT NULL AUTO_INCREMENT, NAME char(32) DEFAULT NULL, PRICE char(32) DEFAULT NULL, DATE_POST char(32) DEFAULT NULL, PRIMARY KEY (ID_STANDING) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

– – Contenu de la table standings –

INSERT INTO standings (ID_STANDING, NAME, PRICE, DATE_POST) VALUES (1, ’junior’, ’15000’, ’1446554128’), (2, ’junior - MEBA’, ’10000’, ’1448548537’);

SELECT r.name as room_name, e.name as employees_name, i.DATE_START, i.DATE_END, i.NUITIES_NUMB FROM rooms r INNER JOIN invoices i USING(ID_ROOM) INNER JOIN employees e USING(ID_EMPLOYEES)

– – Contraintes pour les tables exportées –

– – Contraintes pour la table invoices – ALTER TABLE invoices ADD CONSTRAINT invoices_ibfk_1 FOREIGN KEY (ID_ROOM) REFERENCES rooms (ID_ROOM), ADD CONSTRAINT invoices_ibfk_2 FOREIGN KEY (ID_CUSTOMER) REFERENCES customers (ID_CUSTOMER);

– – Contraintes pour la table employees – ALTER TABLE employees ADD CONSTRAINT EMPLOYEES_ROLE FOREIGN KEY (ID_ROLE) REFERENCES roles (ID_ROLE);

– – Contraintes pour la table rooms – ALTER TABLE rooms ADD CONSTRAINT rooms_ibfk_1 FOREIGN KEY (ID_STANDING) REFERENCES standings (ID_STANDING);

/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /; /!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /; /!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;

+0 -0

Salut

1
2
3
4
INSERT INTO invoices ( ID_INVOICE, ID_ROOM, ID_CUSTOMER, ID_EMPLOYEES, DATE_START, DATE_END, NUITIES_NUMB, DATE_POST) 
VALUES 
(2, 2, 3, ’2015/11/26 15:37’, ’2015/12/03 15:37’, 7, ’1448548672’),
(36, 2, 2, ’2015/11/26 15:37’, ’2015/12/03 15:37’, 7, ’1448548672’);

8 colonnes, 7 valeurs .... Ca ne peut pas marcher :) Tu n’aurais pas oublié de mettre la valeur pour ID_EMPLOYEES ?

+1 -0
Connectez-vous pour pouvoir poster un message.
Connexion

Pas encore membre ?

Créez un compte en une minute pour profiter pleinement de toutes les fonctionnalités de Zeste de Savoir. Ici, tout est gratuit et sans publicité.
Créer un compte