· 4 years ago · Aug 05, 2021, 10:10 AM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema WorldBet
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema WorldBet
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `WorldBet` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
18USE `WorldBet` ;
19
20-- -----------------------------------------------------
21-- Table `WorldBet`.`Arbitro`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `WorldBet`.`Arbitro` (
24 `IdArbitro` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
25 `Nome` VARCHAR(30) NOT NULL,
26 `Cognome` VARCHAR(30) NOT NULL,
27 `Presenze` TINYINT UNSIGNED NULL DEFAULT '0',
28 `Telefono` VARCHAR(15) NULL DEFAULT NULL,
29 PRIMARY KEY (`IdArbitro`))
30ENGINE = InnoDB
31DEFAULT CHARACTER SET = utf8;
32
33
34-- -----------------------------------------------------
35-- Table `WorldBet`.`Clienti`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `WorldBet`.`Clienti` (
38 `IdCliente` INT UNSIGNED NOT NULL AUTO_INCREMENT,
39 `CodFiscale` VARCHAR(16) NOT NULL,
40 `Nome` VARCHAR(30) NOT NULL,
41 `Cognome` VARCHAR(30) NOT NULL,
42 `Telefono` VARCHAR(15) NOT NULL,
43 `Nazione` VARCHAR(50) NOT NULL,
44 `Comune` VARCHAR(50) NOT NULL,
45 `Indirizzo` VARCHAR(60) NOT NULL,
46 `Nascita` DATETIME NOT NULL,
47 PRIMARY KEY (`IdCliente`))
48ENGINE = InnoDB
49DEFAULT CHARACTER SET = utf8;
50
51
52-- -----------------------------------------------------
53-- Table `WorldBet`.`Squadre`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `WorldBet`.`Squadre` (
56 `Nome` VARCHAR(50) NOT NULL,
57 `AllenatoreNome` VARCHAR(30) NOT NULL,
58 `AllenatoreCogn` VARCHAR(30) NOT NULL,
59 PRIMARY KEY (`Nome`))
60ENGINE = InnoDB
61DEFAULT CHARACTER SET = utf8;
62
63
64-- -----------------------------------------------------
65-- Table `WorldBet`.`Edizioni`
66-- -----------------------------------------------------
67CREATE TABLE IF NOT EXISTS `WorldBet`.`Edizioni` (
68 `Anno` SMALLINT UNSIGNED NOT NULL,
69 `Squadra` VARCHAR(50) NOT NULL,
70 PRIMARY KEY (`Anno`),
71 INDEX `edizioni_squadre_idx` (`Squadra` ASC) VISIBLE,
72 CONSTRAINT `edizioni_squadre_fk`
73 FOREIGN KEY (`Squadra`)
74 REFERENCES `WorldBet`.`Squadre` (`Nome`)
75 ON DELETE CASCADE
76 ON UPDATE CASCADE)
77ENGINE = InnoDB
78DEFAULT CHARACTER SET = utf8;
79
80
81-- -----------------------------------------------------
82-- Table `WorldBet`.`Turni`
83-- -----------------------------------------------------
84CREATE TABLE IF NOT EXISTS `WorldBet`.`Turni` (
85 `Nome` VARCHAR(18) NOT NULL,
86 `Girone` TINYINT UNSIGNED NOT NULL,
87 PRIMARY KEY (`Nome`, `Girone`))
88ENGINE = InnoDB
89DEFAULT CHARACTER SET = utf8;
90
91
92-- -----------------------------------------------------
93-- Table `WorldBet`.`Gioca`
94-- -----------------------------------------------------
95CREATE TABLE IF NOT EXISTS `WorldBet`.`Gioca` (
96 `Squadra` VARCHAR(50) NOT NULL,
97 `Turno` VARCHAR(18) NOT NULL,
98 `Girone` TINYINT UNSIGNED NOT NULL,
99 PRIMARY KEY (`Squadra`, `Turno`, `Girone`),
100 INDEX `gioca_turni_idx` (`Turno` ASC, `Girone` ASC) VISIBLE,
101 CONSTRAINT `gioca_squadre_fk`
102 FOREIGN KEY (`Squadra`)
103 REFERENCES `WorldBet`.`Squadre` (`Nome`)
104 ON UPDATE CASCADE,
105 CONSTRAINT `gioca_turni_fk`
106 FOREIGN KEY (`Turno` , `Girone`)
107 REFERENCES `WorldBet`.`Turni` (`Nome` , `Girone`)
108 ON UPDATE CASCADE)
109ENGINE = InnoDB
110DEFAULT CHARACTER SET = utf8;
111
112
113-- -----------------------------------------------------
114-- Table `WorldBet`.`Giocatori`
115-- -----------------------------------------------------
116CREATE TABLE IF NOT EXISTS `WorldBet`.`Giocatori` (
117 `IdGiocatore` MEDIUMINT UNSIGNED NOT NULL,
118 `Nome` VARCHAR(30) NOT NULL,
119 `Cognome` VARCHAR(30) NOT NULL,
120 `Telefono` VARCHAR(15) NULL DEFAULT NULL,
121 `MagliaEuropei` TINYINT UNSIGNED NULL DEFAULT NULL,
122 `SquadraEuropei` VARCHAR(50) NULL DEFAULT NULL,
123 `Squadra` VARCHAR(50) NOT NULL,
124 PRIMARY KEY (`IdGiocatore`),
125 INDEX `giocatori_squadre_idx` (`Squadra` ASC) VISIBLE,
126 CONSTRAINT `giocatori_squadre_fk`
127 FOREIGN KEY (`Squadra`)
128 REFERENCES `WorldBet`.`Squadre` (`Nome`))
129ENGINE = InnoDB
130DEFAULT CHARACTER SET = utf8;
131
132
133-- -----------------------------------------------------
134-- Table `WorldBet`.`Partite`
135-- -----------------------------------------------------
136CREATE TABLE IF NOT EXISTS `WorldBet`.`Partite` (
137 `Numero` MEDIUMINT UNSIGNED NOT NULL,
138 `Turno` VARCHAR(18) NOT NULL,
139 `Girone` TINYINT UNSIGNED NOT NULL,
140 `Esito` ENUM('1', '2', 'X') NULL DEFAULT NULL,
141 `Data` DATETIME NULL DEFAULT NULL,
142 `Citta` VARCHAR(50) NULL DEFAULT NULL,
143 `Molt1` DECIMAL(5,2) UNSIGNED NULL DEFAULT NULL,
144 `Molt2` DECIMAL(5,2) UNSIGNED NULL DEFAULT NULL,
145 `MoltX` DECIMAL(5,2) UNSIGNED NULL DEFAULT NULL,
146 `Squadra1` VARCHAR(50) NOT NULL,
147 `Squadra2` VARCHAR(50) NOT NULL,
148 `Arbitro` SMALLINT UNSIGNED NOT NULL,
149 PRIMARY KEY (`Numero`, `Turno`, `Girone`),
150 INDEX `partite_turni_idx` (`Turno` ASC, `Girone` ASC) VISIBLE,
151 INDEX `partite_squadra1_idx` (`Squadra1` ASC) VISIBLE,
152 INDEX `partite_squadra2_idx` (`Squadra2` ASC) VISIBLE,
153 INDEX `partite_arbitro_idx` (`Arbitro` ASC) VISIBLE,
154 CONSTRAINT `partite_arbitro_fk`
155 FOREIGN KEY (`Arbitro`)
156 REFERENCES `WorldBet`.`Arbitro` (`IdArbitro`)
157 ON UPDATE CASCADE,
158 CONSTRAINT `partite_squadra1_fk`
159 FOREIGN KEY (`Squadra1`)
160 REFERENCES `WorldBet`.`Squadre` (`Nome`)
161 ON UPDATE CASCADE,
162 CONSTRAINT `partite_squadra2_fk`
163 FOREIGN KEY (`Squadra2`)
164 REFERENCES `WorldBet`.`Squadre` (`Nome`)
165 ON UPDATE CASCADE,
166 CONSTRAINT `partite_turni_fk`
167 FOREIGN KEY (`Turno` , `Girone`)
168 REFERENCES `WorldBet`.`Turni` (`Nome` , `Girone`)
169 ON UPDATE CASCADE)
170ENGINE = InnoDB
171DEFAULT CHARACTER SET = utf8;
172
173
174-- -----------------------------------------------------
175-- Table `WorldBet`.`Goal`
176-- -----------------------------------------------------
177CREATE TABLE IF NOT EXISTS `WorldBet`.`Goal` (
178 `IdGoal` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
179 `Minuto` DECIMAL(5,2) UNSIGNED NOT NULL,
180 `Rigore` TINYINT NOT NULL,
181 `Giocatore` MEDIUMINT UNSIGNED NOT NULL,
182 `NumeroPartita` MEDIUMINT UNSIGNED NOT NULL,
183 `Turno` VARCHAR(18) NOT NULL,
184 `Girone` TINYINT UNSIGNED NOT NULL,
185 PRIMARY KEY (`IdGoal`),
186 INDEX `goal_partite_idx` (`NumeroPartita` ASC, `Turno` ASC, `Girone` ASC) VISIBLE,
187 INDEX `goal_giocatori_idx` (`Giocatore` ASC) VISIBLE,
188 CONSTRAINT `goal_giocatori_fk`
189 FOREIGN KEY (`Giocatore`)
190 REFERENCES `WorldBet`.`Giocatori` (`IdGiocatore`)
191 ON DELETE RESTRICT
192 ON UPDATE CASCADE,
193 CONSTRAINT `goal_partite_fk`
194 FOREIGN KEY (`NumeroPartita` , `Turno` , `Girone`)
195 REFERENCES `WorldBet`.`Partite` (`Numero` , `Turno` , `Girone`)
196 ON DELETE RESTRICT
197 ON UPDATE CASCADE)
198ENGINE = InnoDB
199DEFAULT CHARACTER SET = utf8;
200
201
202-- -----------------------------------------------------
203-- Table `WorldBet`.`Scommessa`
204-- -----------------------------------------------------
205CREATE TABLE IF NOT EXISTS `WorldBet`.`Scommessa` (
206 `Codice` VARCHAR(9) NULL DEFAULT NULL,
207 `Scelta` ENUM('1', '2', 'X') NOT NULL,
208 `Esito` TINYINT NULL DEFAULT NULL,
209 `Importo` DECIMAL(8,2) UNSIGNED NOT NULL,
210 `Counter` INT UNSIGNED NOT NULL AUTO_INCREMENT,
211 `Cliente` INT UNSIGNED NOT NULL,
212 `NumeroPartita` MEDIUMINT UNSIGNED NOT NULL,
213 `Turno` VARCHAR(18) NOT NULL,
214 `Girone` TINYINT UNSIGNED NOT NULL,
215 PRIMARY KEY (`Counter`),
216 UNIQUE INDEX `Codice_UNIQUE` (`Codice` ASC) VISIBLE,
217 INDEX `scommessa_clienti_idx` (`Cliente` ASC) VISIBLE,
218 INDEX `scommessa_partite_idx` (`NumeroPartita` ASC, `Turno` ASC, `Girone` ASC) VISIBLE,
219 CONSTRAINT `scommessa_clienti_fk`
220 FOREIGN KEY (`Cliente`)
221 REFERENCES `WorldBet`.`Clienti` (`IdCliente`)
222 ON DELETE RESTRICT
223 ON UPDATE CASCADE,
224 CONSTRAINT `scommessa_partite_fk`
225 FOREIGN KEY (`NumeroPartita` , `Turno` , `Girone`)
226 REFERENCES `WorldBet`.`Partite` (`Numero` , `Turno` , `Girone`)
227 ON DELETE RESTRICT
228 ON UPDATE CASCADE)
229ENGINE = InnoDB
230DEFAULT CHARACTER SET = utf8;
231
232
233-- -----------------------------------------------------
234-- Table `WorldBet`.`Utenti`
235-- -----------------------------------------------------
236CREATE TABLE IF NOT EXISTS `WorldBet`.`Utenti` (
237 `Username` VARCHAR(30) NOT NULL,
238 `Password` VARCHAR(64) NOT NULL,
239 `Ruolo` ENUM('sportello', 'gestore') NOT NULL,
240 PRIMARY KEY (`Username`))
241ENGINE = InnoDB
242DEFAULT CHARACTER SET = utf8;
243
244USE `WorldBet` ;
245
246-- -----------------------------------------------------
247-- procedure account_login
248-- -----------------------------------------------------
249
250DELIMITER $$
251USE `WorldBet`$$
252CREATE DEFINER=`root`@`localhost` PROCEDURE `account_login`(in in_Username varchar(45), in in_Password varchar(45), out out_Ruolo TINYINT)
253BEGIN
254
255 DECLARE var_Ruolo ENUM('sportello', 'gestore');
256 DECLARE var_Password VARCHAR(64);
257 DECLARE salt VARCHAR(32);
258
259 SELECT `Password`
260 INTO var_Password
261 FROM `WorldBet`.`Utenti`
262 WHERE `Username` = in_Username;
263
264 IF var_Password IS NOT NULL THEN
265
266 SET salt = SUBSTRING(var_Password, 33, 32);
267 SELECT `Ruolo`
268 INTO var_Ruolo
269 FROM `WorldBet`.`Utenti`
270 WHERE `Username` = in_Username AND
271 `Password` = CONCAT(md5(CONCAT(in_Password, salt)),salt);
272
273 END IF;
274
275 IF var_Ruolo = 'sportello' THEN
276 SET out_Ruolo = 1;
277 ELSEIF var_Ruolo = 'gestore' THEN
278 SET out_Ruolo = 2;
279 ELSE
280 SET out_Ruolo = 0;
281 END IF;
282
283END$$
284
285DELIMITER ;
286
287-- -----------------------------------------------------
288-- procedure account_registrazione
289-- -----------------------------------------------------
290
291DELIMITER $$
292USE `WorldBet`$$
293CREATE DEFINER=`root`@`localhost` PROCEDURE `account_registrazione`(IN in_Username VARCHAR(30),
294 IN in_Password VARCHAR(30),
295 IN in_Ruolo ENUM('sportello', 'gestore'))
296BEGIN
297 DECLARE salt VARCHAR(32);
298 SET salt = MD5(RAND());
299
300 INSERT INTO `WorldBet`.`Utenti` (`Username`, `Password`, `Ruolo`) VALUES (in_Username, CONCAT(md5(CONCAT(in_Password, salt)),salt), in_Ruolo);
301END$$
302
303DELIMITER ;
304
305-- -----------------------------------------------------
306-- procedure aggiungi_cliente
307-- -----------------------------------------------------
308
309DELIMITER $$
310USE `WorldBet`$$
311CREATE DEFINER=`root`@`localhost` PROCEDURE `aggiungi_cliente`(IN in_CodFiscale VARCHAR(16), IN in_Nome VARCHAR(30), IN in_Cognome VARCHAR(30), IN in_Telefono VARCHAR(15),
312 IN in_Nazione VARCHAR(50), IN in_Comune VARCHAR(50), IN in_Indirizzo VARCHAR(60), IN in_Nascita DATETIME, OUT IdCliente INT)
313BEGIN
314
315 DECLARE exit handler for sqlexception
316 BEGIN
317 rollback;
318 resignal;
319 END;
320
321 SET transaction read write;
322 SET transaction isolation level read committed;
323 START transaction;
324
325 INSERT INTO `WorldBet`.`Clienti` (`CodFiscale`, `Nome`, `Cognome`, `Telefono`, `Nazione`, `Comune`, `Indirizzo`, `Nascita`)
326 VALUES (in_CodFiscale, in_Nome, in_Cognome, in_Telefono, in_Nazione, in_Comune, in_Indirizzo, in_Nascita);
327 set IdCliente = last_insert_id();
328
329 COMMIT;
330
331END$$
332
333DELIMITER ;
334
335-- -----------------------------------------------------
336-- procedure aggiungi_partita
337-- -----------------------------------------------------
338
339DELIMITER $$
340USE `WorldBet`$$
341CREATE DEFINER=`root`@`localhost` PROCEDURE `aggiungi_partita`(IN Turno VARCHAR(18), IN Girone TINYINT UNSIGNED, IN Squadra1 VARCHAR(50), IN Squadra2 VARCHAR(50),
342 IN Arbitro SMALLINT UNSIGNED, IN DataPartita DATETIME, IN Citta VARCHAR(50))
343BEGIN
344
345 DECLARE NumeroPartita MEDIUMINT UNSIGNED;
346 DECLARE exit handler for sqlexception
347 BEGIN
348 rollback;
349 resignal;
350 END;
351
352 SET transaction read write;
353 SET transaction isolation level serializable;
354 START transaction;
355
356 # NumeroPartita = il numero di partita piu grande di un Turno e Girone prefissato
357 SET NumeroPartita = (SELECT Numero
358 FROM `WorldBet`.`Partite`
359 WHERE Partite.Turno = Turno AND Partite.Girone = Girone
360 ORDER BY Numero DESC
361 LIMIT 1);
362
363 IF NumeroPartita IS NOT NULL THEN
364 SET NumeroPartita = NumeroPartita + 1;
365 ELSE
366 SET NumeroPartita = 1;
367 END IF;
368
369 INSERT INTO `WorldBet`.`Partite` (`Numero`,`Turno`, `Girone`, `Data`, `Citta`, `Squadra1`, `Squadra2`, `Arbitro`) VALUES (NumeroPartita, Turno, Girone, DataPartita, Citta, Squadra1, Squadra2, Arbitro);
370
371 COMMIT;
372
373END$$
374
375DELIMITER ;
376
377-- -----------------------------------------------------
378-- procedure aggiungi_scommessa
379-- -----------------------------------------------------
380
381DELIMITER $$
382USE `WorldBet`$$
383CREATE DEFINER=`root`@`localhost` PROCEDURE `aggiungi_scommessa`(IN NumeroPartita MEDIUMINT UNSIGNED, IN Scelta VARCHAR(1), IN Turno VARCHAR(18),
384 IN Girone TINYINT UNSIGNED, IN Importo DECIMAL(8,2) UNSIGNED, IN Cliente INT UNSIGNED)
385BEGIN
386
387 DECLARE LastId INT UNSIGNED;
388 DECLARE var_Scelta ENUM('1','2','X');
389 DECLARE exit handler for sqlexception
390 BEGIN
391 rollback;
392 resignal;
393 END;
394
395 SET var_Scelta = Scelta;
396
397 SET transaction read write;
398 SET transaction isolation level read committed;
399 START transaction;
400
401 # Aggiungi scommessa
402 INSERT INTO `WorldBet`.`Scommessa` (`Scelta`, `Importo`, `Cliente`, `NumeroPartita`, `Turno`, `Girone`)
403 VALUES (var_Scelta, Importo, Cliente, NumeroPartita, Turno, Girone);
404 SET LastId = LAST_INSERT_ID();
405
406 # Crea codice alfanumerico della scommessa
407 UPDATE `WorldBet`.`Scommessa`
408 SET Codice = CONCAT("a", LPAD(HEX(LastId), 8, '0'))
409 WHERE Counter = LastId;
410
411 # Ottieni dati per visualizzare lo scontrino
412 SELECT Scommessa.Codice, Partite.Squadra1, Partite.Squadra2, Scommessa.Scelta,
413 Partite.Turno, Partite.Girone, DATE_FORMAT(Partite.Data, "%Y-%m-%d %H:%i") AS 'Data partita',
414 Scommessa.Importo,
415 CASE
416 WHEN var_Scelta = '1' THEN Partite.Molt1
417 WHEN var_Scelta = '2' THEN Partite.Molt2
418 WHEN var_Scelta = 'X' THEN Partite.MoltX
419 END AS 'Molt.',
420 CASE
421 WHEN var_Scelta = '1' THEN Partite.Molt1 * Importo
422 WHEN var_Scelta = '2' THEN Partite.Molt2 * Importo
423 WHEN var_Scelta = 'X' THEN Partite.MoltX * Importo
424 END AS Vincita, Scommessa.Cliente
425 FROM `WorldBet`.`Partite` INNER JOIN `WorldBet`.`Scommessa` ON
426 Scommessa.NumeroPartita = Partite.Numero AND
427 Scommessa.Turno = Partite.Turno AND
428 Scommessa.Girone = Partite.Girone
429 WHERE Scommessa.Counter = LastId;
430
431 COMMIT;
432
433END$$
434
435DELIMITER ;
436
437-- -----------------------------------------------------
438-- procedure calcola_rosa_squadre
439-- -----------------------------------------------------
440
441DELIMITER $$
442USE `WorldBet`$$
443CREATE DEFINER=`root`@`localhost` PROCEDURE `calcola_rosa_squadre`(IN in_Turno VARCHAR(18))
444BEGIN
445
446 DECLARE var_Successivo VARCHAR(18);
447 DECLARE exit handler for sqlexception
448 BEGIN
449 rollback;
450 resignal;
451 END;
452
453 IF in_Turno = 'qualificazioni' THEN SET var_Successivo = 'sedicesimi';
454 ELSEIF in_Turno = 'sedicesimi' THEN SET var_Successivo = 'ottavi';
455 ELSEIF in_Turno = 'ottavi' THEN SET var_Successivo = 'quarti';
456 ELSEIF in_Turno = 'quarti' THEN SET var_Successivo = 'semifinali';
457 ELSEIF in_Turno = 'semifinali' THEN SET var_Successivo = 'finale';
458 ELSE SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Turno non valido';
459 END IF;
460
461 SET transaction read write;
462 SET transaction isolation level serializable;
463 START transaction;
464
465 DELETE FROM `WorldBet`.`Gioca`
466 WHERE Gioca.Turno = var_Successivo;
467
468 IF in_Turno = 'qualificazioni' THEN
469
470 INSERT INTO `WorldBet`.`Gioca` (`Squadra`, `Turno`, `Girone`)
471 SELECT Squadra, var_Successivo, 0
472 FROM (
473 SELECT Squadra, Turno, Girone, SUM(Punti), ROW_NUMBER() OVER (
474 PARTITION BY Turno, Girone
475 ORDER BY Turno, Girone, SUM(Punti) DESC
476 ) AS Posizione
477 FROM (
478 SELECT Squadra1 AS Squadra, Turno, Girone, SUM(
479 CASE Esito
480 WHEN '1' THEN 3
481 WHEN '2' THEN 0
482 WHEN 'X' THEN 1
483 END) AS Punti
484 FROM `WorldBet`.`Partite`
485 WHERE Partite.Turno = in_Turno
486 GROUP BY Squadra, Turno, Girone
487 UNION ALL
488 SELECT Squadra2 AS Squadra, Turno, Girone, SUM(
489 CASE Esito
490 WHEN '1' THEN 0
491 WHEN '2' THEN 3
492 WHEN 'X' THEN 1
493 END) AS Punti
494 FROM `WorldBet`.`Partite`
495 WHERE Partite.Turno = in_Turno
496 GROUP BY Squadra, Turno, Girone) AS PuntiDelleSquadre
497 GROUP BY Squadra, Turno, Girone) AS ClassificaFinale
498 WHERE Posizione <= 2;
499
500 ELSE
501
502 INSERT INTO `WorldBet`.`Gioca` (`Squadra`, `Turno`, `Girone`)
503 SELECT Squadra1 AS Vincitore, var_Successivo, 0
504 FROM `WorldBet`.`Partite`
505 WHERE Partite.Turno = in_Turno AND Esito = '1'
506 UNION
507 SELECT Squadra2 AS Vincitore, var_Successivo, 0
508 FROM `WorldBet`.`Partite`
509 WHERE Partite.Turno = in_Turno AND Esito = '2';
510
511 END IF;
512
513 COMMIT;
514
515END$$
516
517DELIMITER ;
518
519-- -----------------------------------------------------
520-- procedure imposta_esito_partita
521-- -----------------------------------------------------
522
523DELIMITER $$
524USE `WorldBet`$$
525CREATE DEFINER=`root`@`localhost` PROCEDURE `imposta_esito_partita`(IN in_Numero MEDIUMINT UNSIGNED, IN in_Turno VARCHAR(18), IN in_Girone TINYINT UNSIGNED, IN Esito_in VARCHAR(1))
526BEGIN
527
528 DECLARE in_Esito ENUM('1', '2', 'X');
529 DECLARE var_Numero MEDIUMINT;
530 DECLARE exit handler for sqlexception
531 BEGIN
532 rollback;
533 resignal;
534 END;
535
536 SET in_Esito = Esito_in;
537 SET transaction read write;
538 SET transaction isolation level read committed;
539 START transaction;
540
541 # seleziona la partita della quale si vuole impostare l'esito
542 SELECT Numero
543 INTO var_Numero
544 FROM `WorldBet`.`Partite`
545 WHERE `Numero` = in_Numero AND `Turno` = in_Turno AND `Girone` = in_Girone;
546
547 # controllo se la partita esiste
548 IF var_Numero IS NULL THEN
549 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Partita non esistente';
550 ELSE
551 UPDATE `WorldBet`.`Partite` SET `Esito` = in_Esito WHERE (`Numero` = in_Numero) and (`Turno` = in_Turno) and (`Girone` = in_Girone);
552 END IF;
553
554 # per ogni scommessa di quella partita impostane l'esito
555 UPDATE `WorldBet`.`Scommessa`
556 SET `Esito` = (CASE WHEN `Scelta` = in_Esito THEN 1
557 WHEN `Scelta` <> in_Esito THEN 0
558 END)
559 WHERE (`NumeroPartita` = in_Numero) and (`Turno` = in_Turno) and (`Girone` = in_Girone);
560
561 COMMIT;
562
563END$$
564
565DELIMITER ;
566
567-- -----------------------------------------------------
568-- procedure imposta_moltiplicatori_partita
569-- -----------------------------------------------------
570
571DELIMITER $$
572USE `WorldBet`$$
573CREATE DEFINER=`root`@`localhost` PROCEDURE `imposta_moltiplicatori_partita`(IN in_Numero MEDIUMINT UNSIGNED, IN in_Turno VARCHAR(18), IN in_Girone TINYINT UNSIGNED,
574 IN in_Molt1 DECIMAL(5,2) UNSIGNED, IN in_Molt2 DECIMAL(5,2) UNSIGNED, IN in_MoltX DECIMAL(5,2) UNSIGNED)
575BEGIN
576
577 DECLARE exit handler for sqlexception
578 BEGIN
579 rollback;
580 resignal;
581 END;
582
583 SET transaction read write;
584 SET transaction isolation level read committed;
585 START transaction;
586
587 UPDATE `WorldBet`.`Partite` SET `Molt1` = in_Molt1, `Molt2` = in_Molt2, `MoltX` = in_MoltX
588 WHERE (`Numero` = in_Numero) AND (`Turno` = in_Turno) AND (`Girone` = in_Girone);
589
590 COMMIT;
591
592END$$
593
594DELIMITER ;
595
596-- -----------------------------------------------------
597-- procedure visualizza_arbitri
598-- -----------------------------------------------------
599
600DELIMITER $$
601USE `WorldBet`$$
602CREATE DEFINER=`root`@`localhost` PROCEDURE `visualizza_arbitri`()
603BEGIN
604
605 SET transaction read only;
606 SET transaction isolation level read committed;
607 START transaction;
608
609 SELECT IdArbitro, Nome, Cognome
610 FROM `WorldBet`.`Arbitro`;
611
612 COMMIT;
613
614END$$
615
616DELIMITER ;
617
618-- -----------------------------------------------------
619-- procedure visualizza_esito_scommessa
620-- -----------------------------------------------------
621
622DELIMITER $$
623USE `WorldBet`$$
624CREATE DEFINER=`root`@`localhost` PROCEDURE `visualizza_esito_scommessa`(IN in_Codice VARCHAR(9), OUT out_Esito TINYINT UNSIGNED)
625BEGIN
626
627 DECLARE var_Codice VARCHAR(9);
628
629 SET transaction read only;
630 SET transaction isolation level read committed;
631 START transaction;
632
633 SELECT Codice, Esito
634 INTO var_Codice, out_Esito
635 FROM `WorldBet`.`Scommessa`
636 WHERE Scommessa.Codice = in_Codice;
637
638 IF out_Esito IS NULL THEN
639 IF var_Codice IS NULL THEN
640 SET out_Esito = 3;
641 ELSE
642 SET out_Esito = 2;
643 END IF;
644 END IF;
645
646 COMMIT;
647
648END$$
649
650DELIMITER ;
651
652-- -----------------------------------------------------
653-- procedure visualizza_partite_abilitate
654-- -----------------------------------------------------
655
656DELIMITER $$
657USE `WorldBet`$$
658CREATE DEFINER=`root`@`localhost` PROCEDURE `visualizza_partite_abilitate`()
659BEGIN
660
661 SET transaction read only;
662 SET transaction isolation level read committed;
663 START transaction;
664
665 SELECT Numero AS 'Numero partita', Turno, Girone, DATE_FORMAT(Partite.Data, "%Y-%m-%d %H:%i") AS 'Data partita', Squadra1, Squadra2, Molt1, Molt2, MoltX
666 FROM `WorldBet`.`Partite`
667 WHERE Esito IS NULL AND Molt1 IS NOT NULL AND
668 Molt2 IS NOT NULL AND MoltX IS NOT NULL
669 ORDER BY Data ASC;
670
671 COMMIT;
672
673END$$
674
675DELIMITER ;
676
677-- -----------------------------------------------------
678-- procedure visualizza_partite_senza_esito
679-- -----------------------------------------------------
680
681DELIMITER $$
682USE `WorldBet`$$
683CREATE DEFINER=`root`@`localhost` PROCEDURE `visualizza_partite_senza_esito`()
684BEGIN
685
686 SET transaction read only;
687 SET transaction isolation level read committed;
688 START transaction;
689
690 SELECT Numero AS 'Numero partita', Turno, Girone, Squadra1 AS 'Squadra 1',
691 Squadra2 AS 'Squadra 2', DATE_FORMAT(Data, "%Y-%m-%d %H:%i") AS 'Data partita',
692 Citta
693 FROM `WorldBet`.`Partite`
694 WHERE Partite.Esito IS NULL
695 ORDER BY Data ASC;
696
697 COMMIT;
698
699END$$
700
701DELIMITER ;
702
703-- -----------------------------------------------------
704-- procedure visualizza_partite_senza_moltiplicatore
705-- -----------------------------------------------------
706
707DELIMITER $$
708USE `WorldBet`$$
709CREATE DEFINER=`root`@`localhost` PROCEDURE `visualizza_partite_senza_moltiplicatore`()
710BEGIN
711
712 SET transaction read only;
713 SET transaction isolation level read committed;
714 START transaction;
715
716 SELECT Numero AS 'Numero partita', Turno, Girone, Squadra1 AS 'Squadra 1',
717 Squadra2 AS 'Squadra 2', DATE_FORMAT(Data, "%Y-%m-%d %H:%i") AS 'Data partita',
718 Citta
719 FROM `WorldBet`.`Partite`
720 WHERE Partite.Molt1 IS NULL AND Partite.Molt2 IS NULL AND Partite.MoltX IS NULL
721 ORDER BY Data ASC;
722
723 COMMIT;
724
725END$$
726
727DELIMITER ;
728
729-- -----------------------------------------------------
730-- procedure visualizza_squadre_nel_turno
731-- -----------------------------------------------------
732
733DELIMITER $$
734USE `WorldBet`$$
735CREATE DEFINER=`root`@`localhost` PROCEDURE `visualizza_squadre_nel_turno`(IN in_Turno VARCHAR(18), IN in_Girone TINYINT UNSIGNED)
736BEGIN
737
738 SET transaction read only;
739 SET transaction isolation level repeatable read;
740 START transaction;
741
742 IF in_Turno = "qualificazioni" THEN
743 SELECT Squadra AS 'Nome squadra'
744 FROM `WorldBet`.`Gioca`
745 WHERE Gioca.Turno = in_Turno AND Gioca.Girone = in_Girone;
746 ELSE
747 # Mostra le squadre che giocano nel dato turno
748 # Non mostrare le squadre che hanno gia giocato una partita
749 SELECT Squadra AS 'Nome squadra'
750 FROM `WorldBet`.`Gioca`
751 WHERE Gioca.Turno = in_Turno AND Gioca.Girone = in_Girone AND
752 Squadra NOT IN (SELECT Squadra1 AS Squadra
753 FROM `WorldBet`.`Partite`
754 WHERE Partite.Turno = in_Turno AND Partite.Girone = in_Girone
755 UNION
756 SELECT Squadra2 AS Squadra
757 FROM `WorldBet`.`Partite`
758 WHERE Partite.Turno = in_Turno AND Partite.Girone = in_Girone);
759 END IF;
760
761
762 COMMIT;
763
764END$$
765
766DELIMITER ;
767USE `WorldBet`;
768
769DELIMITER $$
770USE `WorldBet`$$
771CREATE
772DEFINER=`root`@`localhost`
773TRIGGER `WorldBet`.`Partite_controllo_esito`
774BEFORE UPDATE ON `WorldBet`.`Partite`
775FOR EACH ROW
776BEGIN
777 IF OLD.Turno <> 'qualificazioni' AND NEW.Esito = 'X' THEN
778 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Il pareggio è possibile solo nelle qualificazioni';
779 END IF;
780END$$
781
782USE `WorldBet`$$
783CREATE
784DEFINER=`root`@`localhost`
785TRIGGER `WorldBet`.`Partite_controllo_moltiplicatori`
786BEFORE UPDATE ON `WorldBet`.`Partite`
787FOR EACH ROW
788BEGIN
789 IF NEW.Molt1 <= 1 OR NEW.Molt2 <= 1 OR NEW.MoltX <= 1 THEN
790 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'I moltiplicatori devono assumere valori maggiori di 1';
791 END IF;
792END$$
793
794USE `WorldBet`$$
795CREATE
796DEFINER=`root`@`localhost`
797TRIGGER `WorldBet`.`Partite_controllo_numero_partecipazioni`
798BEFORE INSERT ON `WorldBet`.`Partite`
799FOR EACH ROW
800BEGIN
801
802 DECLARE num INT;
803 DECLARE msg VARCHAR(100);
804
805 IF NEW.Turno <> 'qualificazioni' THEN
806 # conto il numero di partite giocate da Squadra1 in un dato turno e girone
807 SELECT count(*)
808 INTO num
809 FROM `WorldBet`.`Partite`
810 WHERE (Partite.Squadra1 = NEW.Squadra1 OR Partite.Squadra2 = NEW.Squadra1) AND
811 Partite.Turno = NEW.Turno AND
812 Partite.Girone = NEW.Girone;
813
814 IF num <> 0 THEN
815 SET msg = (SELECT CONCAT(NEW.Squadra1, ' ha già giocato una partita in questo turno'));
816 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
817 END IF;
818
819 # conto il numero di partite giocate da Squadra2 in un dato turno e girone
820 SELECT count(*)
821 INTO num
822 FROM `WorldBet`.`Partite`
823 WHERE (Partite.Squadra1 = NEW.Squadra2 OR Partite.Squadra2 = NEW.Squadra2) AND
824 Partite.Turno = NEW.Turno AND
825 Partite.Girone = NEW.Girone;
826
827 IF num <> 0 THEN
828 SET msg = (SELECT CONCAT(NEW.Squadra2, ' ha già giocato una partita in questo turno'));
829 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
830 END IF;
831
832 END IF;
833
834END$$
835
836USE `WorldBet`$$
837CREATE
838DEFINER=`root`@`localhost`
839TRIGGER `WorldBet`.`Partite_controllo_partecipazioni_turno`
840BEFORE INSERT ON `WorldBet`.`Partite`
841FOR EACH ROW
842BEGIN
843
844 DECLARE num INT;
845 DECLARE msg VARCHAR(100);
846
847 # num1 = 1 se Squadra1 gioca in quel turno ed in quel girone
848 SET num = (SELECT count(*)
849 FROM `WorldBet`.`Gioca`
850 WHERE Gioca.Squadra = NEW.Squadra1 AND
851 Gioca.Turno = NEW.Turno AND
852 Gioca.Girone = NEW.Girone);
853 IF num = 0 THEN
854 SET msg = (SELECT CONCAT(NEW.Squadra1, ' non è abilitata a giocare in ', NEW.Turno, '-', NEW.Girone));
855 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
856 END IF;
857
858 # num2 = 1 se Squadra2 gioca in quel turno ed in quel girone
859 SET num = (SELECT count(*)
860 FROM `WorldBet`.`Gioca`
861 WHERE Gioca.Squadra = NEW.Squadra2 AND
862 Gioca.Turno = NEW.Turno AND
863 Gioca.Girone = NEW.Girone);
864 IF num = 0 THEN
865 SET msg = (SELECT CONCAT(NEW.Squadra2, ' non è abilitata a giocare in ', NEW.Turno, '-', NEW.Girone));
866 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
867 END IF;
868
869END$$
870
871USE `WorldBet`$$
872CREATE
873DEFINER=`root`@`localhost`
874TRIGGER `WorldBet`.`Partite_controllo_squadre_uguali`
875BEFORE INSERT ON `WorldBet`.`Partite`
876FOR EACH ROW
877BEGIN
878 IF NEW.Squadra1 = NEW.Squadra2 THEN
879 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Selezionare due squadre diverse';
880 END IF;
881END$$
882
883USE `WorldBet`$$
884CREATE
885DEFINER=`root`@`localhost`
886TRIGGER `WorldBet`.`Scommessa_controllo_scelta`
887BEFORE INSERT ON `WorldBet`.`Scommessa`
888FOR EACH ROW
889BEGIN
890 IF NEW.Turno <> 'qualificazioni' AND NEW.Scelta = 'X' THEN
891 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Il pareggio è possibile solo nelle qualificazioni';
892 END IF;
893END$$
894
895USE `WorldBet`$$
896CREATE
897DEFINER=`root`@`localhost`
898TRIGGER `WorldBet`.`Scommessa_verifica_partita`
899BEFORE INSERT ON `WorldBet`.`Scommessa`
900FOR EACH ROW
901BEGIN
902
903 DECLARE var_Esito ENUM('1', '2', 'X');
904 DECLARE var_Molt1 DECIMAL(5,2);
905 DECLARE var_Molt2 DECIMAL(5,2);
906 DECLARE var_MoltX DECIMAL(5,2);
907
908 SELECT Esito, Molt1, Molt2, MoltX
909 INTO var_Esito, var_Molt1, var_Molt2, var_MoltX
910 FROM `WorldBet`.`Partite`
911 WHERE Partite.Numero = NEW.NumeroPartita AND
912 Partite.Turno = NEW.Turno AND
913 Partite.Girone = NEW.Girone;
914
915 IF (var_Molt1 IS NULL) OR (var_Molt2 IS NULL) OR (var_MoltX IS NULL) THEN
916 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La partita non presenta moltiplicatori di vincita';
917 END IF;
918
919 IF var_Esito IS NOT NULL THEN
920 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Le scommesse su questa partita sono chiuse';
921 END IF;
922
923END$$
924
925
926DELIMITER ;
927
928SET SQL_MODE=@OLD_SQL_MODE;
929SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
930SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
931