<?php

function renderSubmission($db) {
	$familien_id = $_REQUEST["familie"];
	$ids = getGästeId($db, $familien_id);

	/**
	 * Zusage
	 */
	if ( "zusage" == $_REQUEST["zusage"] ) {
		foreach( $ids as $_id ) {
			$id = $_id["id"];
			$stmt = $db->prepare("UPDATE gästeliste SET zusage = ? WHERE id = ?");
			$stmt->execute(array(1, $id));
		}
	} else if ( "absage" == $_REQUEST["zusage"] ) {
		foreach( $ids as $_id ) {
			$id = $_id["id"];
			$stmt = $db->prepare("UPDATE gästeliste SET zusage = ? WHERE id = ?");
			$stmt->execute(array(0, $id));
		}
	} else {
		foreach( $ids as $_id ) {
			$id = $_id["id"];
			if ( "zusage" == $_REQUEST[ $id . "_zusage" ]) {
				$stmt = $db->prepare("UPDATE gästeliste SET zusage = ? WHERE id = ?");
				$stmt->execute(array(1, $id));
			} else {
				$stmt = $db->prepare("UPDATE gästeliste set zusage = ? WHERE id = ?");
				$stmt->execute(array(1, $id));
			}
			#$stmt = $db->prepare("INSERT INTO gästeliste (zusage) values (?) WHERE id = ?");
			#var_dump($id . ": " . $_REQUEST[ $id . "_zusage" ]);
		}
	}

	/**
	 * Auto
	 */
	if ( "zusage" == $_REQUEST["auto"] ) {
		$stmt = $db->prepare("UPDATE familie SET kommt_mit_auto = ?, auto_freie_plätze = ? WHERE id = ?");
		$stmt->execute(array(1, (int)$_REQUEST["plätze"], $familien_id));
	} else if ( "absage" == $_REQUEST["auto"] ) {
		$stmt = $db->prepare("UPDATE familie SET kommt_mit_auto = ? WHERE id = ?");
		$stmt->execute(array(0, $familien_id));
	} else {
		$stmt = $db->prepare("UPDATE familie SET kommt_mit_auto = ? WHERE id = ?");
		$stmt->execute(array(2, $familien_id));
	}

	/**
	 * Essenwahl
	 */
	foreach( $ids as $_id ) {
		$id = $_id["id"];

		if ( "mit_fleisch" ==  $_REQUEST[$id . "_essenwahl"]) {
			$stmt = $db->prepare("UPDATE gästeliste SET essenwahl = ? WHERE id = ?");
			$stmt->execute(array(0, $id));
		} else if ( "vegan" ==  $_REQUEST[$id . "_essenwahl"] ) {
			$stmt = $db->prepare("UPDATE gästeliste SET essenwahl = ? WHERE id = ?");
			$stmt->execute(array(2, $id));
		} else if ( "kinder" ==  $_REQUEST[$id . "_essenwahl"] ) {
			$stmt = $db->prepare("UPDATE gästeliste SET essenwahl = ? WHERE id = ?");
			$stmt->execute(array(3, $id));
		} else {
			$stmt = $db->prepare("UPDATE gästeliste SET essenwahl = ? WHERE id = ?");
			$stmt->execute(array(1, $id));
		}
	}

	$stmt = $db->prepare("UPDATE familie SET bemerkungsfeld = ? WHERE id = ?");
	$stmt->execute(array($_REQUEST["bemerkungsfeld"], $familien_id));

	if ( "" != $_REQUEST["buffetbeitrag"] ) {
		$stmt = $db->prepare("SELECT 1 FROM buffet WHERE familien_id = ?");
		$stmt->execute(array($familien_id));
		$ok = $stmt->fetchAll();
		if ( empty($ok) ) {
			$stmt = $db->prepare("INSERT INTO buffet VALUES (null, ?, ?)");
			$stmt->execute(array($_REQUEST["buffetbeitrag"], (int)$familien_id));
		} else {
			$stmt = $db->prepare("UPDATE buffet SET eintrag = ? WHERE familien_id = ?");
			$stmt->execute(array($_REQUEST["buffetbeitrag"], (int)$familien_id));
		}
	}

	return getData($db, $familien_id);
}


function getGästeId($db, $familien_id) {
	$stmt = $db->prepare("SELECT id FROM gästeliste WHERE familien_id = ?");
	$stmt->execute(array($familien_id));
	return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getData($db, $familien_id = null) {
	$query = "
		SELECT
			short_link,
			bemerkungsfeld,
			kommt_mit_auto,
			auto_freie_plätze,gästeliste.id as id,
			name,
			familien_id,
			essenwahl,
			zusage
		FROM
			familie
		JOIN
			gästeliste on familie.id = gästeliste.familien_id
		WHERE
	";

	if ( is_null($familien_id) ) {

		$stmt = $db->prepare( $query . " short_link = ?");
		$stmt->execute(array(ltrim($_SERVER["REQUEST_URI"],"/")));

	} else {

		$stmt = $db->prepare( $query . " familien_id = ?");
		$stmt->execute(array($familien_id));

	}
	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();
}