Fehlerbericht/app/db.php

<?php
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, PUT');
header('Access-Control-Allow-Headers: accept, content-type');
header('Cache-Control: no-cache, must-revalidate');
header('Content-type: application/json');

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

$data = json_decode(file_get_contents('php://input'), true);

switch ($_REQUEST['a']) {
	case 'fehlerbericht':
		$kunde = $_REQUEST['kunde'];
		$datum = $_REQUEST['datum'];
		$start = $_REQUEST['start'];

		if (count($data) > 0) {

		} else {
			$q = $dbh->query("SELECT sm.*, ifnull(sk.benutzer, '') as benutzer, k.whitelist FROM status_meldung sm
			 				  INNER JOIN kunden k USING (kunde)
			 				  LEFT JOIN status_kommentar sk USING (datum, kunde, start)
							  WHERE sm.datum = '{$datum}' AND sm.kunde = '{$kunde}' AND sm.start = '{$start}' LIMIT 1");
			$result = $q->fetch(PDO::FETCH_ASSOC);
			$result['fehlerbericht'] = str_replace("\r\`", "`", str_replace("\r\"", "\"", str_replace("\r\n", "", $result['fehlerbericht'])));
			$result['fehlerbericht'] = json_decode($result['fehlerbericht'], true);
			$result['whitelist'] = json_decode(stripslashes($result['whitelist']), true);
		}
		break;

	case 'zeit':
		$q = $dbh->exec("UPDATE kunden SET start_soll = '{$_REQUEST['start_soll']}', ende_soll = '{$_REQUEST['ende_soll']}' WHERE kunde = '{$_REQUEST['kunde']}' ");
		$error = $dbh->errorInfo();
		if ($error[0] != '00000') {
			$result = $error;
		} else {
			$result = $_REQUEST;
		}
		break;

	case 'whitelist':
		$q = $dbh->query("SELECT * FROM kunden WHERE kunde = '{$_REQUEST['kunde']}' ");
		$result = $q->fetch(PDO::FETCH_ASSOC);
		$whitelist = json_decode(stripslashes($result['whitelist']), true);
		if (!is_array($whitelist)) {
			$whitelist = array();
		}
		if ($_REQUEST['aktiv'] == "J") {
			if (!isset($whitelist[$_REQUEST['typ']])) {
				$whitelist[$_REQUEST['typ']] = array($_REQUEST['wert']);
			} else if (in_array($_REQUEST['wert'], $whitelist[$_REQUEST['typ']])) {
				$result = array('whitelist' => $whitelist);
				break;
			} else {
				$whitelist[$_REQUEST['typ']][] = $_REQUEST['wert'];
			}
		} else {
			if (!isset($whitelist[$_REQUEST['typ']]) || !in_array($_REQUEST['wert'], $whitelist[$_REQUEST['typ']])) break;
			$whitelist[$_REQUEST['typ']] = array_diff($whitelist[$_REQUEST['typ']], array($_REQUEST['wert']));
		}

		$q = $dbh->exec("UPDATE kunden SET whitelist = '" . addslashes(json_encode($whitelist)) . "' WHERE kunde = '{$_REQUEST['kunde']}' ");
		$error = $dbh->errorInfo();
		if ($error[0] != '00000') {
			$result = $error;
		} else {
			$result = array('whitelist' => $whitelist);
		}
		break;

	case 'kunden':
		if (count($data) > 0) {
			foreach ($data as $kunde) {
				if (isset($kunde['kunde'])) {
					$q = $dbh->exec("UPDATE kunden SET system = '{$kunde['system']}', start_soll = '{$kunde['start_soll']}', ende_soll = '{$kunde['ende_soll']}',
									erster_status = '{$kunde['erster_status']}', aktiv = '{$kunde['aktiv']}', woche = '{$kunde['woche']}',
									bundesland = '{$kunde['bundesland']}', plz = '{$kunde['plz']}', ort = '{$kunde['ort']}' WHERE kunde = '{$kunde['kunde']}' ");
				}
			}
		}
		$q = $dbh->query("SELECT * FROM kunden");
		$result = $q->fetchAll(PDO::FETCH_ASSOC);
		break;

	case 'config':
		$q = $dbh->query("SELECT kunde, datum FROM kunden_config");
		$result = array('options' => array());
		while ($row = $q->fetch(PDO::FETCH_ASSOC)) {
			$result['options'][$row['kunde']][] = $row['datum'];
		}

		if (isset($_REQUEST['kunde']) && $_REQUEST['kunde'] != 'undefined') {
			if (preg_match('/\d{4}-\d{2}-\d{2}/', $_REQUEST['datum'])) {
				$q = $dbh->query("SELECT * FROM kunden_config WHERE kunde = '{$_REQUEST['kunde']}' AND datum = '{$_REQUEST['datum']}'");
			} else {
				$q = $dbh->query("SELECT * FROM kunden_config WHERE kunde = '{$_REQUEST['kunde']}' ORDER BY datum DESC LIMIT 1");
			}
			if ($row = $q->fetch(PDO::FETCH_ASSOC)) {
				$row['info'] = json_decode($row['info'], true);
				$result['current'] = $row;
			}
		}
		break;
	case 'benutzer':
		$q = $dbh->query("SELECT * FROM benutzer");
		$result = $q->fetchAll(PDO::FETCH_ASSOC);
		break;
	case 'kommentar':
		$id = (isset($_REQUEST['id'])) ? $_REQUEST['id'] : '';

		if (count($data) > 0) {
			if ($id == '') {
				$now = date('Y-m-d H:m:i');
				$q = $dbh->exec("INSERT INTO status_kommentar (kunde, datum, start, benutzer, fehler, status, kommentar, cdate)
								 VALUES ('{$data['kunde']}','{$data['datum']}','{$data['start']}','{$data['benutzer']}','{$data['fehler']}','{$data['status']}','{$data['kommentar']}','{$now}')");
				$id = $dbh->lastInsertId();

				if ($data['status'] == '7' && $data['benutzer2'] != '') {
					$q = $dbh->exec("INSERT INTO status_kommentar (kunde, datum, start, benutzer, fehler, status, kommentar, cdate)
								     VALUES ('{$data['kunde']}','{$data['datum']}','{$data['start']}','{$data['benutzer2']}','{$data['fehler']}','2','','{$now}')");
					$id = $dbh->lastInsertId();
				}

				$q = $dbh->exec("UPDATE status_meldung SET bearbeitet = '1', kommentar_id = '{$id}'
							 WHERE kunde = '{$data['kunde']}' AND datum = '{$data['datum']}' AND start = '{$data['start']}' ");

			} else {
				$q = $dbh->exec("UPDATE status_kommentar SET
									benutzer = '{$data['benutzer']}',
									fehler = '{$data['fehler']}',
									status = '{$data['status']}',
									kommentar = '{$data['kommentar']}'
								 WHERE id = '{$id}' ");
			}
		}
		$q = $dbh->query("SELECT * FROM status_kommentar
						  WHERE datum > date_add(now(), INTERVAL -32 DAY)
						  ORDER BY datum DESC, mdate DESC");
		$result = $q->fetchAll(PDO::FETCH_ASSOC);
		break;
	default:
	case 'liste':
		$datum = (!isset($_REQUEST['datum']) || $_REQUEST['datum'] == '') ? "a.datum > date_add(now(), INTERVAL -32 DAY) AND k.erster_status <= a.datum" : "a.datum = '{$_REQUEST['datum']}' ";
		$kunde = (!isset($_REQUEST['kunde']) || $_REQUEST['kunde'] == '') ? "1" : "k.kunde LIKE '%{$_REQUEST['kunde']}%'";

		$q = $dbh->query("SELECT a.datum, k.kunde, k.system, a.bundesland, a.feiertag, k.start_soll, k.ende_soll, sm.start, sm.ende, sm.anzahl, sm.bearbeitet,
								timediff(sm.ende, k.ende_soll) - timediff(sm.start, k.start_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(sk.status <= 1, 0, 1) * if(sm.anzahl > 0 or (sm.anzahl is null and now() > concat(a.datum, ' ', k.ende_soll)), 1, 0) as fehler,
								if(sm.anzahl is null and now() > concat(a.datum, ' ', k.ende_soll), 1, 0) as fehlend,
								ifnull(sk.benutzer, '') as benutzer
							FROM arbeitstage a
							INNER JOIN kunden k USING (bundesland)
							LEFT JOIN status_meldung sm USING (datum, kunde)
							LEFT JOIN status_kommentar sk ON sm.kommentar_id = sk.id
							WHERE {$datum} AND {$kunde} AND a.datum <= now()
							AND k.aktiv = 1
							ORDER BY a.datum, k.ende_soll");
		$result = $q->fetchAll(PDO::FETCH_ASSOC);

		if (isset($_REQUEST['events'])) {
			$color = array("green", "#bb0", "#b80", "#d55", "#c33", "#a00", '?' => "#999");

			$events = array();
			foreach ($result as $e) {
				if ($e['anzahl'] != '0') {
					$event = array();
					$anzahl = (!is_null($e['anzahl'])) ? $e['anzahl'] : "?";
					$ende = ($e['ende']) ? $e['ende'] : $e['ende_soll'];
					$event['title'] = "{$e['kunde']} ({$anzahl})";
					$event['start'] = "{$e['datum']}T{$ende}";
					$event['end'] = date("Y-m-d H:i:s", strtotime("+1 hour", strtotime($event['start'])));
					$event['color'] = ($anzahl > 4) ? "darkred" : $color[$anzahl];
					$events[] = $event;
				}
			}
			$result = $events;
		}
}
echo json_encode($result);