Stats!

1 files changed, 46 insertions, 0 deletions

diff --git a/functions.php b/functions.php
index e31ce0f..3444bf9 100644
--- a/functions.php
+++ b/functions.php
@@ -126,3 +126,49 @@ function getData($db, $familien_id = null) {
 	}
 	return $stmt->fetchAll();
 }
+
+function getStats($db) {
+	$query = "
+with freie_plätze as (
+select sum(auto_freie_plätze) as freie_plätze,
+SUM(CASE WHEN kommt_mit_auto = 0
+                THEN 1
+                ELSE 0 END) AS kommt_ohne_auto,
+SUM(CASE WHEN kommt_mit_auto = 2
+                THEN 1
+                ELSE 0 END) AS kommt_eventuell_mit_auto,
+SUM(kommt_mit_auto) as auto_zusage
+from familie
+)
+select
+freie_plätze,
+auto_zusage as kommt_mit_auto,
+kommt_ohne_auto,
+kommt_eventuell_mit_auto,
+sum(zusage) as zusage,
+SUM(CASE WHEN f.kommt_mit_auto = 0
+                OR f.kommt_mit_auto = 2
+                THEN 1
+                ELSE 0 END) AS benötigte_auto_plätze,
+SUM(CASE WHEN zusage = 0
+                THEN 1
+                ELSE 0 END) AS absage,
+SUM(CASE WHEN essenwahl = 0
+                THEN 1
+                ELSE 0 END) AS ente,
+SUM(CASE WHEN essenwahl = 1
+                THEN 1
+                ELSE 0 END) AS vegetarisch,
+SUM(CASE WHEN essenwahl = 2
+                THEN 1
+                ELSE 0 END) AS vegan,
+SUM(CASE WHEN essenwahl = 3
+                THEN 1
+                ELSE 0 END) AS kinderteller
+ from familie f join gästeliste g on f.id = familien_id join freie_plätze
+";
+	$stmt = $db->prepare($query);
+	$stmt->execute();
+
+	return $stmt->fetchAll();
+}