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