· 8 years ago · Jan 16, 2018, 02:52 PM
1-- MySQL Script generated by MySQL Workbench
2-- Tue Jan 16 15:26:18 2018
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `mydb` ;
14
15-- -----------------------------------------------------
16-- Schema mydb
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
19USE `mydb` ;
20
21-- -----------------------------------------------------
22-- Table `mydb`.`customers`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `mydb`.`customers` ;
25
26CREATE TABLE IF NOT EXISTS `mydb`.`customers` (
27 `id` INT NOT NULL AUTO_INCREMENT,
28 `firstname` VARCHAR(45) NULL,
29 `lastname` VARCHAR(64) NULL,
30 `mail` VARCHAR(254) NULL,
31 `phone` VARCHAR(45) NULL,
32 PRIMARY KEY (`id`))
33ENGINE = InnoDB;
34
35
36-- -----------------------------------------------------
37-- Table `mydb`.`status`
38-- -----------------------------------------------------
39DROP TABLE IF EXISTS `mydb`.`status` ;
40
41CREATE TABLE IF NOT EXISTS `mydb`.`status` (
42 `id` INT NOT NULL AUTO_INCREMENT,
43 `title` VARCHAR(45) NULL,
44 PRIMARY KEY (`id`))
45ENGINE = InnoDB;
46
47
48-- -----------------------------------------------------
49-- Table `mydb`.`bookings`
50-- -----------------------------------------------------
51DROP TABLE IF EXISTS `mydb`.`bookings` ;
52
53CREATE TABLE IF NOT EXISTS `mydb`.`bookings` (
54 `id` INT NOT NULL AUTO_INCREMENT,
55 `customer_id` INT NOT NULL,
56 `status_id` INT NOT NULL,
57 `participants` TINYINT(3) NULL,
58 `message` VARCHAR(512) NULL,
59 `date` DATE NULL,
60 PRIMARY KEY (`id`, `customer_id`),
61 CONSTRAINT `fk_bookings_customers1`
62 FOREIGN KEY (`customer_id`)
63 REFERENCES `mydb`.`customers` (`id`)
64 ON DELETE NO ACTION
65 ON UPDATE NO ACTION,
66 CONSTRAINT `fk_bookings_status1`
67 FOREIGN KEY (`status_id`)
68 REFERENCES `mydb`.`status` (`id`)
69 ON DELETE NO ACTION
70 ON UPDATE NO ACTION)
71ENGINE = InnoDB;
72
73CREATE INDEX `fk_bookings_customers1_idx` ON `mydb`.`bookings` (`customer_id` ASC);
74
75CREATE INDEX `fk_bookings_status1_idx` ON `mydb`.`bookings` (`status_id` ASC);
76
77
78-- -----------------------------------------------------
79-- Table `mydb`.`services`
80-- -----------------------------------------------------
81DROP TABLE IF EXISTS `mydb`.`services` ;
82
83CREATE TABLE IF NOT EXISTS `mydb`.`services` (
84 `id` INT NOT NULL AUTO_INCREMENT,
85 `title` VARCHAR(45) NULL,
86 PRIMARY KEY (`id`))
87ENGINE = InnoDB;
88
89
90-- -----------------------------------------------------
91-- Table `mydb`.`workshops`
92-- -----------------------------------------------------
93DROP TABLE IF EXISTS `mydb`.`workshops` ;
94
95CREATE TABLE IF NOT EXISTS `mydb`.`workshops` (
96 `id` INT NOT NULL AUTO_INCREMENT,
97 `title` VARCHAR(64) NULL,
98 `desc` VARCHAR(512) NULL,
99 PRIMARY KEY (`id`))
100ENGINE = InnoDB;
101
102
103-- -----------------------------------------------------
104-- Table `mydb`.`slots`
105-- -----------------------------------------------------
106DROP TABLE IF EXISTS `mydb`.`slots` ;
107
108CREATE TABLE IF NOT EXISTS `mydb`.`slots` (
109 `id` INT NOT NULL AUTO_INCREMENT,
110 `day_of_week` TINYINT(3) NULL,
111 `title` VARCHAR(45) NULL,
112 `service_id` INT NOT NULL,
113 PRIMARY KEY (`id`, `service_id`),
114 CONSTRAINT `fk_slots_services1`
115 FOREIGN KEY (`service_id`)
116 REFERENCES `mydb`.`services` (`id`)
117 ON DELETE NO ACTION
118 ON UPDATE NO ACTION)
119ENGINE = InnoDB;
120
121CREATE INDEX `fk_slots_services1_idx` ON `mydb`.`slots` (`service_id` ASC);
122
123
124-- -----------------------------------------------------
125-- Table `mydb`.`bookings_has_workshops`
126-- -----------------------------------------------------
127DROP TABLE IF EXISTS `mydb`.`bookings_has_workshops` ;
128
129CREATE TABLE IF NOT EXISTS `mydb`.`bookings_has_workshops` (
130 `booking_id` INT NOT NULL,
131 `workshop_id` INT NOT NULL,
132 PRIMARY KEY (`booking_id`, `workshop_id`),
133 CONSTRAINT `fk_bookings_has_workshops_bookings1`
134 FOREIGN KEY (`booking_id`)
135 REFERENCES `mydb`.`bookings` (`id`)
136 ON DELETE NO ACTION
137 ON UPDATE NO ACTION,
138 CONSTRAINT `fk_bookings_has_workshops_workshops1`
139 FOREIGN KEY (`workshop_id`)
140 REFERENCES `mydb`.`workshops` (`id`)
141 ON DELETE NO ACTION
142 ON UPDATE NO ACTION)
143ENGINE = InnoDB;
144
145CREATE INDEX `fk_bookings_has_workshops_workshops1_idx` ON `mydb`.`bookings_has_workshops` (`workshop_id` ASC);
146
147CREATE INDEX `fk_bookings_has_workshops_bookings1_idx` ON `mydb`.`bookings_has_workshops` (`booking_id` ASC);
148
149
150-- -----------------------------------------------------
151-- Table `mydb`.`services_has_workshops`
152-- -----------------------------------------------------
153DROP TABLE IF EXISTS `mydb`.`services_has_workshops` ;
154
155CREATE TABLE IF NOT EXISTS `mydb`.`services_has_workshops` (
156 `service_id` INT NOT NULL,
157 `workshop_id` INT NOT NULL,
158 PRIMARY KEY (`service_id`, `workshop_id`),
159 CONSTRAINT `fk_services_has_workshops_services1`
160 FOREIGN KEY (`service_id`)
161 REFERENCES `mydb`.`services` (`id`)
162 ON DELETE NO ACTION
163 ON UPDATE NO ACTION,
164 CONSTRAINT `fk_services_has_workshops_workshops1`
165 FOREIGN KEY (`workshop_id`)
166 REFERENCES `mydb`.`workshops` (`id`)
167 ON DELETE NO ACTION
168 ON UPDATE NO ACTION)
169ENGINE = InnoDB;
170
171CREATE INDEX `fk_services_has_workshops_workshops1_idx` ON `mydb`.`services_has_workshops` (`workshop_id` ASC);
172
173CREATE INDEX `fk_services_has_workshops_services1_idx` ON `mydb`.`services_has_workshops` (`service_id` ASC);
174
175
176-- -----------------------------------------------------
177-- Table `mydb`.`bookings_has_slots`
178-- -----------------------------------------------------
179DROP TABLE IF EXISTS `mydb`.`bookings_has_slots` ;
180
181CREATE TABLE IF NOT EXISTS `mydb`.`bookings_has_slots` (
182 `booking_id` INT NOT NULL,
183 `slot_id` INT NOT NULL,
184 PRIMARY KEY (`booking_id`, `slot_id`),
185 CONSTRAINT `fk_bookings_has_slots_bookings1`
186 FOREIGN KEY (`booking_id`)
187 REFERENCES `mydb`.`bookings` (`id`)
188 ON DELETE NO ACTION
189 ON UPDATE NO ACTION,
190 CONSTRAINT `fk_bookings_has_slots_slots1`
191 FOREIGN KEY (`slot_id`)
192 REFERENCES `mydb`.`slots` (`id`)
193 ON DELETE NO ACTION
194 ON UPDATE NO ACTION)
195ENGINE = InnoDB;
196
197CREATE INDEX `fk_bookings_has_slots_slots1_idx` ON `mydb`.`bookings_has_slots` (`slot_id` ASC);
198
199CREATE INDEX `fk_bookings_has_slots_bookings1_idx` ON `mydb`.`bookings_has_slots` (`booking_id` ASC);
200
201
202-- -----------------------------------------------------
203-- Table `mydb`.`calendar_table`
204-- -----------------------------------------------------
205DROP TABLE IF EXISTS `mydb`.`calendar_table` ;
206
207CREATE TABLE IF NOT EXISTS `mydb`.`calendar_table` (
208 `dt` DATE NOT NULL,
209 `y` SMALLINT NULL DEFAULT NULL,
210 `q` TINYINT NULL DEFAULT NULL,
211 `m` TINYINT NULL DEFAULT NULL,
212 `d` TINYINT NULL DEFAULT NULL,
213 `dw` TINYINT NULL DEFAULT NULL,
214 `monthName` VARCHAR(9) NULL DEFAULT NULL,
215 `dayName` VARCHAR(9) NULL DEFAULT NULL,
216 `w` TINYINT NULL DEFAULT NULL,
217 `isWeekday` BINARY(1) NULL DEFAULT NULL,
218 `isHoliday` BINARY(1) NULL DEFAULT NULL,
219 `holidayDescr` VARCHAR(32) NULL DEFAULT NULL,
220 `isPayday` BINARY(1) NULL DEFAULT NULL,
221 PRIMARY KEY (`dt`));
222
223
224-- -----------------------------------------------------
225-- Data for table `mydb`.`customers`
226-- -----------------------------------------------------
227START TRANSACTION;
228USE `mydb`;
229INSERT INTO `mydb`.`customers` (`id`, `firstname`, `lastname`, `mail`, `phone`) VALUES (1, 'Admin', 'Test', 'admin@test.com', '0611223344');
230
231COMMIT;
232
233
234-- -----------------------------------------------------
235-- Data for table `mydb`.`status`
236-- -----------------------------------------------------
237START TRANSACTION;
238USE `mydb`;
239INSERT INTO `mydb`.`status` (`id`, `title`) VALUES (1, 'Pré-Réservé');
240INSERT INTO `mydb`.`status` (`id`, `title`) VALUES (2, 'Réservé');
241
242COMMIT;
243
244
245-- -----------------------------------------------------
246-- Data for table `mydb`.`services`
247-- -----------------------------------------------------
248START TRANSACTION;
249USE `mydb`;
250INSERT INTO `mydb`.`services` (`id`, `title`) VALUES (1, 'Visite');
251INSERT INTO `mydb`.`services` (`id`, `title`) VALUES (2, 'Atelier');
252INSERT INTO `mydb`.`services` (`id`, `title`) VALUES (3, 'Anniversaire');
253INSERT INTO `mydb`.`services` (`id`, `title`) VALUES (4, 'Centre');
254INSERT INTO `mydb`.`services` (`id`, `title`) VALUES (5, 'Groupe');
255
256COMMIT;
257
258
259-- -----------------------------------------------------
260-- Data for table `mydb`.`workshops`
261-- -----------------------------------------------------
262START TRANSACTION;
263USE `mydb`;
264INSERT INTO `mydb`.`workshops` (`id`, `title`, `desc`) VALUES (1, 'workshop1', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donec consectetur facilisis arcu, ullamcorper fringilla magna euismod et. Pellentesque neque nisi, malesuada vitae placerat et, pretium vel tortor.');
265INSERT INTO `mydb`.`workshops` (`id`, `title`, `desc`) VALUES (2, 'workshop2', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donec consectetur facilisis arcu, ullamcorper fringilla magna euismod et. Pellentesque neque nisi, malesuada vitae placerat et, pretium vel tortor.');
266INSERT INTO `mydb`.`workshops` (`id`, `title`, `desc`) VALUES (3, 'workshop3', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donec consectetur facilisis arcu, ullamcorper fringilla magna euismod et. Pellentesque neque nisi, malesuada vitae placerat et, pretium vel tortor.');
267INSERT INTO `mydb`.`workshops` (`id`, `title`, `desc`) VALUES (4, 'workshop4', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donec consectetur facilisis arcu, ullamcorper fringilla magna euismod et. Pellentesque neque nisi, malesuada vitae placerat et, pretium vel tortor.');
268
269COMMIT;
270
271
272-- -----------------------------------------------------
273-- Data for table `mydb`.`slots`
274-- -----------------------------------------------------
275START TRANSACTION;
276USE `mydb`;
277INSERT INTO `mydb`.`slots` (`id`, `day_of_week`, `title`, `service_id`) VALUES (1, 3, 'Slot1', 3);
278INSERT INTO `mydb`.`slots` (`id`, `day_of_week`, `title`, `service_id`) VALUES (2, 6, 'Slot2', 3);
279INSERT INTO `mydb`.`slots` (`id`, `day_of_week`, `title`, `service_id`) VALUES (3, 6, 'Slot3', 3);
280INSERT INTO `mydb`.`slots` (`id`, `day_of_week`, `title`, `service_id`) VALUES (4, 6, 'Slot4', 3);
281INSERT INTO `mydb`.`slots` (`id`, `day_of_week`, `title`, `service_id`) VALUES (5, 0, 'Slot5', 3);
282INSERT INTO `mydb`.`slots` (`id`, `day_of_week`, `title`, `service_id`) VALUES (6, 0, 'Slot6', 3);
283INSERT INTO `mydb`.`slots` (`id`, `day_of_week`, `title`, `service_id`) VALUES (7, 0, 'Slot7', 3);
284
285COMMIT;
286
287
288-- -----------------------------------------------------
289-- Data for table `mydb`.`services_has_workshops`
290-- -----------------------------------------------------
291START TRANSACTION;
292USE `mydb`;
293INSERT INTO `mydb`.`services_has_workshops` (`service_id`, `workshop_id`) VALUES (3, 1);
294INSERT INTO `mydb`.`services_has_workshops` (`service_id`, `workshop_id`) VALUES (3, 3);
295INSERT INTO `mydb`.`services_has_workshops` (`service_id`, `workshop_id`) VALUES (3, 5);
296INSERT INTO `mydb`.`services_has_workshops` (`service_id`, `workshop_id`) VALUES (3, 8);
297INSERT INTO `mydb`.`services_has_workshops` (`service_id`, `workshop_id`) VALUES (3, 12);
298INSERT INTO `mydb`.`services_has_workshops` (`service_id`, `workshop_id`) VALUES (3, 13);
299INSERT INTO `mydb`.`services_has_workshops` (`service_id`, `workshop_id`) VALUES (3, 2);
300INSERT INTO `mydb`.`services_has_workshops` (`service_id`, `workshop_id`) VALUES (3, 6);
301INSERT INTO `mydb`.`services_has_workshops` (`service_id`, `workshop_id`) VALUES (3, 9);
302
303COMMIT;
304
305SELECT DISTINCT b3.date AS unavailable_date
306FROM bookings b3
307LEFT JOIN bookings_has_slots bhs3
308ON bhs3.booking_id = b3.id
309LEFT JOIN slots s3
310ON s3.id = bhs3.slot_id
311WHERE NOT EXISTS (
312 SELECT null
313 FROM slots s
314 LEFT JOIN bookings_has_slots bhs
315 ON s.id = bhs.slot_id
316 LEFT JOIN bookings b
317 ON bhs.booking_id = b.id
318
319 WHERE NOT EXISTS (
320 SELECT null
321 FROM bookings_has_slots bhs2
322 LEFT JOIN bookings b2
323 ON bhs2.booking_id = b2.id
324 WHERE b.date = b2.date
325 )
326 AND s.service_id = :serviceId
327 AND s.day_of_week = s3.day_of_week
328)
329
330SELECT s.id, s.day_of_week, s.title
331FROM slots s
332LEFT JOIN bookings_has_slots bhs
333ON s.id = bhs.slot_id
334LEFT JOIN bookings b
335ON bhs.booking_id = b.id
336
337WHERE NOT EXISTS (
338 SELECT null
339 FROM bookings_has_slots bhs2
340 LEFT JOIN bookings b2
341 ON bhs2.booking_id = b2.id
342 WHERE b.date = :selectedDate
343)
344AND s.service_id = :serviceId
345AND s.day_of_week = DAYOFWEEK(:selectedDate)