<?php
$dbh = new PDO("mysql:host=localhost;dbname=tasks", "root", "gc01mysql");
$dbh->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);

if (isset($_GET['kunde']) && isset($_GET['datum'])) {
	$q = $dbh->query("SELECT * FROM statusmail WHERE kunde = '{$_GET['kunde']}' AND datum = '{$_GET['datum']}' LIMIT 1");
	$result = $q->fetch(PDO::FETCH_ASSOC);
	$result['fehlerbericht'] = str_replace("\\", "\\\\", str_replace("\r\"", "\"", str_replace("\r\n", "", $result['fehlerbericht'])));
	$result['fehlerbericht'] = json_decode($result['fehlerbericht'], true);

} else if (isset($_GET['kunden'])) {
	$q = $dbh->query("SELECT * FROM kunden");
	$result = $q->fetchAll(PDO::FETCH_ASSOC);
} else {
	$date = date("Y-m");

	$q = $dbh->query("SELECT a.datum, k.kunde, left(k.system, 1) as system, a.bundesland, a.feiertag, k.start_soll, k.ende_soll, s.start, s.ende, s.anzahl, s.bearbeitet, s.benutzer, s.kommentar,
							if(s.anzahl is null or s.anzahl > 0, 1, 0) as fehler,
							timediff(k.ende_soll, k.start_soll) as dauer,
							timediff(s.ende, k.ende_soll) as abweichung,
							case
							 when woche = 'Mo-Fr' then mofr
							 when woche = 'Mo-Sa' then mosa
							 when woche = 'Mo-So' then moso
							 when woche = 'Di-So' then diso
							 else 1
							end * if(anzahl is null and now() > concat(a.datum, ' ', k.ende_soll), 1, 0) as fehlt
						FROM arbeitstage a
						INNER JOIN kunden k USING (bundesland)
						LEFT JOIN statusmail s USING (datum, kunde)
						WHERE datum LIKE '{$date}%' AND k.erster_status <= a.datum AND a.datum <= now()
						AND k.aktiv = 1
						ORDER BY a.datum, k.ende_soll");
	$result = $q->fetchAll(PDO::FETCH_ASSOC);
}

header('Access-Control-Allow-Origin: *');
header('Cache-Control: no-cache, must-revalidate');
header('Content-type: application/json');
echo json_encode($result);