119 lines
8.0 KiB
SQL
119 lines
8.0 KiB
SQL
USE project;
|
|
|
|
/*
|
|
---------------------------------- 1 ----------------------------------
|
|
Zu jeder Person soll aufgezeichnet werden, wann dies Person wieviel von
|
|
welchem Produkt zu sich nimmt. Daraus soll eine Sicht erstellt werden, die anzeigt, wie viel
|
|
Prozent der Tagesmenge erreicht wurde und wieviel von welchen Nährstoffen konsumiert
|
|
wurde. Idealerweise gibt es ein Rechtekonzept dafür (ggfs. als SQL-Kommentar im Skript).
|
|
Wird dabei gegen Vorgaben (Kalorienvorgaben, vegetarisch, Allergene, etc.) verstoßen, so
|
|
soll das in der Sicht angezeigt werden. Wenn der Tag „ok“ ist, soll das markiert werden,
|
|
ansonsten soll angezeigt werden, gegen was verstoßen wurde.
|
|
-----------------------------------------------------------------------
|
|
|
|
Rechtekonzept:
|
|
Als normaler Benutzer sollte man nur Zugriff auf seine eigenen Daten haben.
|
|
Als privilegierter Nutzer (z.B. Admin) kann man alle Daten sehen
|
|
*/
|
|
DROP VIEW IF EXISTS Menu_Naehrwerte;
|
|
CREATE VIEW Menu_Naehrwerte AS
|
|
(
|
|
SELECT Menuname,
|
|
GROUP_CONCAT(DISTINCT Produktname SEPARATOR ', ') AS 'Produkte',
|
|
ROUND(SUM(Fett * (SELECT Menge FROM Menge WHERE MengenID = M.MengenID) / 100), 2) AS 'Fett',
|
|
ROUND(SUM(Kohlenhydrate * (SELECT Menge FROM Menge WHERE MengenID = M.MengenID) / 100), 2) AS 'Kohlenhydrate',
|
|
ROUND(SUM(Eiweiss * (SELECT Menge FROM Menge WHERE MengenID = M.MengenID) / 100), 2) AS 'Eiweiss',
|
|
ROUND(SUM(Brennwert * (SELECT Menge FROM Menge WHERE MengenID = M.MengenID) / 100), 2) AS 'Brennwert',
|
|
GROUP_CONCAT(DISTINCT Allergenbezeichnung SEPARATOR ', ') AS 'Allergene',
|
|
GROUP_CONCAT(DISTINCT E_Nummer SEPARATOR ', ') AS 'Zusatzstoff'
|
|
FROM Menu
|
|
JOIN Menucontent M on Menu.MenuID = M.MenuID
|
|
JOIN Menge M3 on M.MengenID = M3.MengenID
|
|
JOIN Produkt P on M3.ProduktID = P.ProduktID
|
|
JOIN Naehrwerte N on P.ProduktID = N.ProduktID
|
|
|
|
JOIN enthaeltAllergen eA on P.ProduktID = eA.ProduktID
|
|
JOIN Allergen A on A.AllergenID = eA.AllergenID
|
|
|
|
LEFT JOIN enthaeltZusatzstoff eZ on P.ProduktID = eZ.ProduktID
|
|
GROUP BY Menuname);
|
|
|
|
|
|
DROP VIEW IF EXISTS Aufgabe_5;
|
|
CREATE VIEW Aufgabe_5 AS
|
|
(
|
|
SELECT Personenvorname,
|
|
Personenname,
|
|
Naehrwertziel,
|
|
MN.Brennwert,
|
|
CONCAT(ROUND(MN.Brennwert / Naehrwertziel * 100, 2), '%') AS Naehrwertziel_Prozent,
|
|
MN.Fett,
|
|
MN.Kohlenhydrate,
|
|
MN.Eiweiss,
|
|
IF(MN.Brennwert / Naehrwertziel * 100 <= 100, 'OK', 'Naehrwertziel überschritten') AS Naehrwert_OK,
|
|
IF((SELECT COUNT(*)
|
|
FROM (SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(Allergene, ', ', n.digit + 1), ', ', -1) AS Allergen
|
|
FROM Menu_Naehrwerte
|
|
INNER JOIN
|
|
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
|
|
ON LENGTH(REPLACE(Allergene, ', ', '')) <= LENGTH(Allergene) - n.digit
|
|
WHERE Menuname = M.Menuname
|
|
UNION ALL
|
|
SELECT Allergenbezeichnung
|
|
FROM Allergie AS A20
|
|
JOIN Allergen A10 on A20.AllergenID = A10.AllergenID
|
|
JOIN Person P10 on A20.PersonID = P10.PersonID
|
|
WHERE Personenname = P.Personenname) as u_all) = (SELECT COUNT(*)
|
|
FROM (SELECT DISTINCT SUBSTRING_INDEX(
|
|
SUBSTRING_INDEX(Allergene, ', ', n.digit + 1),
|
|
', ', -1) AS Allergen
|
|
FROM Menu_Naehrwerte
|
|
INNER JOIN
|
|
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
|
|
ON LENGTH(REPLACE(Allergene, ', ', '')) <= LENGTH(Allergene) - n.digit
|
|
WHERE Menuname = M.Menuname
|
|
UNION
|
|
SELECT Allergenbezeichnung
|
|
FROM Allergie AS A20
|
|
JOIN Allergen A10 on A20.AllergenID = A10.AllergenID
|
|
JOIN Person P10 on A20.PersonID = P10.PersonID
|
|
WHERE Personenname = P.Personenname) as u),
|
|
'OK',
|
|
'Allergenhaltiges Produkt konsumiert') AS 'Allergene_OK',
|
|
IF((SELECT COUNT(*)
|
|
FROM (SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(Zusatzstoff, ', ', n.digit + 1), ', ',
|
|
-1) AS Zusatzstoff
|
|
FROM Menu_Naehrwerte
|
|
INNER JOIN
|
|
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
|
|
ON LENGTH(REPLACE(Zusatzstoff, ', ', '')) <= LENGTH(Zusatzstoff) - n.digit
|
|
WHERE Menuname = M.Menuname
|
|
UNION ALL
|
|
SELECT Z.E_Nummer
|
|
FROM Zusatzstoffunvertraeglichkeit AS Z20
|
|
JOIN Zusatzstoff Z on Z20.E_Nummer = Z.E_Nummer
|
|
JOIN Person P10 on Z20.PersonID = P10.PersonID
|
|
WHERE Personenname = P.Personenname) as u_all) = (SELECT COUNT(*)
|
|
FROM (SELECT DISTINCT SUBSTRING_INDEX(
|
|
SUBSTRING_INDEX(Zusatzstoff, ', ', n.digit + 1),
|
|
', ',
|
|
-1) AS Zusatzstoff
|
|
FROM Menu_Naehrwerte
|
|
INNER JOIN
|
|
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
|
|
ON LENGTH(REPLACE(Zusatzstoff, ', ', '')) <=
|
|
LENGTH(Zusatzstoff) - n.digit
|
|
WHERE Menuname = M.Menuname
|
|
UNION
|
|
SELECT Z.E_Nummer
|
|
FROM Zusatzstoffunvertraeglichkeit AS Z20
|
|
JOIN Zusatzstoff Z on Z20.E_Nummer = Z.E_Nummer
|
|
JOIN Person P10 on Z20.PersonID = P10.PersonID
|
|
WHERE Personenname = P.Personenname) as u),
|
|
'OK',
|
|
'Zusatzstoffhaltiges Produkt konsumiert') AS 'Zusatzstoff_OK'
|
|
FROM konsumiert
|
|
JOIN Menu M on M.MenuID = konsumiert.MenuID
|
|
JOIN Person P on P.PersonID = konsumiert.PersonID
|
|
JOIN Menu_Naehrwerte MN on M.Menuname = MN.Menuname);
|