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);
$result = array();

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

		if (count($data) > 0) {

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

				$result['whitelist'] = (isset($result['whitelist'])) ? json_decode(stripslashes($result['whitelist']), true) : null;
			} else {
				$result = array();
			}
			if (isset($result['kommentar_id']) && $result['kommentar_id'] > 0) {
				$q = $dbh->query("SELECT sk.* FROM status_kommentar sk
							      WHERE sk.id = '{$result['kommentar_id']}' ");
				$comment = $q->fetch(PDO::FETCH_ASSOC);	
			} else {
				$q = $dbh->query("SELECT sk.* FROM status_kommentar sk
							      WHERE sk.datum = '{$datum}' AND sk.kunde = '{$kunde}' AND sk.start = '{$start}' 
								  ORDER BY cdate DESC LIMIT 1");
				$comment = $q->fetch(PDO::FETCH_ASSOC);
			}
			$result['kommentar'] = ($comment) ? $comment : array();
		}
		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 'changelog':
		$kunde = "Jansen";
		$q = $dbh->query("SELECT * FROM kunden_gcstarter WHERE kunde = '{$kunde}' ORDER BY datum DESC LIMIT 1");
		$result = $q->fetch(PDO::FETCH_ASSOC);
		$r = json_decode(utf8_decode($result['gcstarter']), true);
		//echo json_last_error();
		$byDate = (isset($r['Kontenzuordnung GAPS.csv.log'])) ? $r['Kontenzuordnung GAPS.csv.log'] : array();

		$states = array("neu", "akt", "entf");

		$list = array();

		foreach($byDate as $entry) {
			foreach ($states as $state) {
				foreach($entry[$state] as $account) {
					$account['Datum'] = $entry['datum'];
					$account['Status'] = $state;
					$list[] = $account;
				}
			}
		}

		$q = $dbh->query("SELECT * FROM kunden_gcstruct WHERE kunde = '{$kunde}' ORDER BY datum DESC LIMIT 1");
		$result = $q->fetch(PDO::FETCH_ASSOC);
		$struct = json_decode($result['gcstruct'], true);

		$result = array('Historie' => $byDate, 'Liste' => $list, 'GCStruct' => $struct);

		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);
		foreach ($result as $i => $r) {
			$result[$i]['whitelist'] = json_decode(stripslashes($r['whitelist']), true);
			$result[$i]['ort'] = utf8_decode($r['ort']);
		}
		break;

	case 'tickets':
		if (count($data) > 0) {
			if ($data['id'] == '') {
				$q = $dbh->exec("INSERT INTO tickets (datum, benutzer, kunde, kontakt, beschreibung, termin, dauer, status, prioritaet, fortschritt, kategorie, programm, kommentar)
									VALUES ('{$data['datum']}', '{$data['benutzer']}', '{$data['kunde']}', '{$data['kontakt']}', '{$data['beschreibung']}', '{$data['termin']}', '{$data['dauer']}',
										'{$data['status']}', '{$data['prioritaet']}', '{$data['fortschritt']}', '{$data['kategorie']}', '{$data['programm']}', '{$data['kommentar']}')");
			} else {
				$q = $dbh->exec("UPDATE tickets SET datum = '{$data['datum']}', benutzer = '{$data['benutzer']}', kunde = '{$data['kunde']}', kontakt = '{$data['kontakt']}',
										beschreibung = '{$data['beschreibung']}', termin = '{$data['termin']}', dauer = '{$data['dauer']}',	status = '{$data['status']}',
										prioritaet = '{$data['prioritaet']}', fortschritt = '{$data['fortschritt']}', kategorie = '{$data['kategorie']}', programm = '{$data['programm']}',
										kommentar = '{$data['kommentar']}'
								 WHERE id = '{$data['id']}' ");
			}
		}
		$q = $dbh->query("SELECT * FROM tickets");
		$result = $q->fetchAll(PDO::FETCH_ASSOC);
		break;

	case 'config':
		$q = $dbh->query("SELECT kunde, datum FROM kunden_config ORDER BY 1, 2 DESC");
		$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'] != "undefined") ? $_REQUEST['id'] : '';

		if (count($data) > 0) {
			if ($id == '') {
				$now = date('Y-m-d H:i:s');
				$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':
		$query = file_get_contents(dirname(__FILE__)."/../queries/status_tag.sql");

		if (isset($_REQUEST['datum']) && $_REQUEST['datum'] != '' && $_REQUEST['datum'] != 'today') {
		  $query = str_replace("now()", "'" . $_REQUEST['datum'] . " 23:59:59'", $query);
    }
    //echo $query;
    $q = $dbh->query($query);

    while ($row = $q->fetch(PDO::FETCH_ASSOC)) {
      $ka = $row['kunde'] . "-" . $row['aufgabe'];
      if (!isset($result[$ka])) {
        $result[$ka][0]['status'] = 'egal';
      }
      $result[$ka][$row['datum_diff']] = $row;
    }
}
$json = json_encode($result);
$hash = sha1($json);
echo '{"result":'.$json.',"hash":"'.$hash.'"}';
// echo json_last_error();