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