· 6 years ago · Nov 27, 2019, 09:02 AM
1/*Operazione 5*/
2
3DROP PROCEDURE IF EXISTS ClassificazioneFertilita; $$
4
5CREATE PROCEDURE ClassificazioneFertilita(IN SpecieTarget VARCHAR(50), IN SessoTarget CHAR, IN Fertili BOOL, IN Malati BOOL)
6BEGIN
7 DROP TEMPORARY TABLE IF EXISTS CapiTarget;
8 CREATE TEMPORARY TABLE CapiTarget
9 (
10 Animale INT PRIMARY KEY
11 )ENGINE=InnoDB DEFAULT CHARSET=latin1;
12
13 CASE
14 WHEN Fertili = FALSE AND Malati = FALSE
15 THEN
16 INSERT INTO CapiTarget
17 SELECT A.Codice
18 FROM Animale
19 WHERE A.Sesso = SessoTarget
20 AND A.Specie = SpecieTarget;
21
22 WHEN SessoTarget = "F" AND Fertili = TRUE AND Malati = FALSE
23 THEN
24 INSERT INTO CapiTarget
25 SELECT A1.Codice
26 FROM Animale A1
27 WHERE A1.Sesso = SessoTarget
28 AND A1.Specie = SpecieTarget
29 AND NOT EXISTS
30 (
31 SELECT *
32 FROM Animale A2 INNER JOIN Riproduzione R
33 INNER JOIN Gravidanza G INNER JOIN Parto P INNER JOIN Animale A3
34 ON A2.Codice = R.Madre
35 AND R.Codice = G.Riproduzione
36 AND G.Codice = P.Gravidanza
37 AND P.Figlio = A3.Codice
38 WHERE A2.Codice = A1.Codice
39 AND A3.DataNascita > CURRENT_DATE - INTERVAL 1 YEAR
40 );
41
42 WHEN Fertili = FALSE AND Malati = TRUE
43 THEN
44 INSERT INTO CapiTarget
45 SELECT A.Codice
46 FROM Animale A
47 WHERE A.Sesso = SessoTarget
48 AND A.Specie = SpecieTarget
49 AND NOT EXISTS
50 (
51 SELECT *
52 FROM Esordio E INNER JOIN Visita V
53 ON E.Visita = V.Codice
54 WHERE V.Animale = A.Codice
55 AND E.DataGuarigione IS NOT NULL
56 );
57
58 WHEN SessoTarget = "F" AND Fertili = TRUE AND Malati = TRUE
59 THEN
60 INSERT INTO CapiTarget
61 SELECT A.Codice
62 FROM Animale A
63 WHERE A.Sesso = SessoTarget
64 AND A.Specie = SpecieTarget
65 AND NOT EXISTS
66 (
67 SELECT *
68 FROM Esordio E INNER JOIN Visita V
69 ON E.Visita = V.Codice
70 WHERE V.Animale = A.Codice
71 AND E.DataGuarigione IS NOT NULL
72 )
73 AND NOT EXISTS
74 (
75 SELECT *
76 FROM Animale A2 INNER JOIN Riproduzione R
77 INNER JOIN Gravidanza G INNER JOIN Animale A3
78 ON A2.Codice = R.Madre
79 AND R.Codice = G.Riproduzione
80 AND G.Codice = P.Gravidanza
81 AND P.Figlio = A3.Codice
82 WHERE A2.Codice = A.Codice
83 AND A3.DataNascita > CURRENT_DATE - INTERVAL 1 YEAR
84 );
85 END CASE;
86
87 DROP TEMPORARY TABLE IF EXISTS Tentativi;
88 CREATE TEMPORARY TABLE Tentativi
89 (
90 Animale INT PRIMARY KEY,
91 Tentativi INT DEFAULT 0
92 )ENGINE=InnoDB DEFAULT CHARSET=latin1;
93
94 INSERT INTO Tentativi
95 SELECT CT.Animale, COUNT(*)
96 FROM CapiTarget CT INNER JOIN Riproduzione R1
97 ON (CT.Animale = R1.Madre OR CT.Animale = R1.Padre)
98 GROUP BY CT.Animale
99 UNION
100 SELECT CT.Animale, 0
101 FROM CapiTarget CT LEFT OUTER JOIN Riproduzione R1
102 ON (CT.Animale = R1.Madre OR CT.Animale = R1.Padre)
103 WHERE R1.Codice IS NULL;
104
105 DROP TEMPORARY TABLE IF EXISTS Successi;
106 CREATE TEMPORARY TABLE Successi
107 (
108 Animale INT PRIMARY KEY,
109 Successi INT DEFAULT 0
110 )ENGINE=InnoDB DEFAULT CHARSET=latin1;
111
112 INSERT INTO Successi
113 SELECT CT.Animale, COUNT(*) AS Successi
114 FROM CapiTarget CT INNER JOIN Riproduzione R1 INNER JOIN Gravidanza G
115 ON (CT.Animale = R1.Madre OR CT.Animale = R1.Padre)
116 AND G.Riproduzione = R1.Codice
117 WHERE R1.Esito = "Successo"
118 OR G.Stato = "Successo"
119 GROUP BY CT.Animale
120 UNION
121 SELECT CT.Animale, 0
122 FROM CapiTarget CT LEFT OUTER JOIN Riproduzione R1
123 ON (CT.Animale = R1.Madre OR CT.Animale = R1.Padre)
124 WHERE R1.Codice IS NULL;
125
126 SELECT T.Animale, IF(T.Tentativi > 0, ((S.Successi * 100) / T.Tentativi), 0) AS PercentualeSuccesso,
127 DENSE_RANK() OVER(
128 ORDER BY PercentualeSuccesso DESC
129 ) AS Classifica
130 FROM Tentativi T INNER JOIN Successi S
131 ON T.Animale = S.Animale
132 ORDER BY PercentualeSuccesso DESC;
133
134END $$
135
136DROP PROCEDURE IF EXISTS ClassificazioneProduttivita; $$
137
138CREATE OR REPLACE VIEW QuantitaLatteMungitura AS
139SELECT M.Codice, M.Animale, M.DataEOra, SUM(CM.Quantita) AS QuantitaLatte
140FROM Mungitura M INNER JOIN ComponentiMungitura CM
141 ON M.Codice = CM.Mungitura; $$
142
143CREATE PROCEDURE ClassificazioneProduttivita(IN SpecieTarget VARCHAR(50), IN Malati BOOL)
144BEGIN
145 DROP TEMPORARY TABLE IF EXISTS CapiTarget;
146 CREATE TEMPORARY TABLE IF NOT EXISTS CapiTarget
147 (
148 Animale INT PRIMARY KEY
149 )ENGINE=InnoDB DEFAULT CHARSET=latin1;
150
151 CASE
152 WHEN Malati = FALSE
153 THEN
154 INSERT INTO CapiTarget
155 SELECT A.Codice
156 FROM Animale A
157 WHERE A.Sesso = "F"
158 AND A.Specie = SpecieTarget;
159
160 WHEN Malati = TRUE
161 THEN
162 INSERT INTO CapiTarget
163 SELECT A.Codice
164 FROM Animale A
165 WHERE A.Sesso = "F"
166 AND A.Specie = SpecieTarget
167 AND NOT EXISTS
168 (
169 SELECT *
170 FROM Esordio E INNER JOIN Visita V
171 ON E.Visita = V.Codice
172 WHERE V.Animale = A.Codice
173 AND E.DataGuarigione IS NOT NULL
174 );
175 END CASE;
176
177 SELECT QLM.Animale, AVG(QLM.QuantitaLatte) AS Media,
178 DENSE_RANK() OVER(
179 ORDER BY Media DESC
180 ) AS Classifica
181 FROM CapiTarget CT INNER JOIN QuantitaLatteMungitura QLM
182 ON CT.Animale = QLM.Animale
183 WHERE DATE(QLM.DataEOra) >= CURRENT_TIMESTAMP - INTERVAL 1 MONTH
184 GROUP BY QLM.Animale
185 ORDER BY Media DESC;
186END $$
187
188CALL ClassificazioneFertilita("Ovini", "F", True, FALSE); $$
189CALL ClassificazioneProduttivita("Ovini", FALSE); $$