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=192.168.2.41;dbname=tasks;charset=utf8", "gaps", "Gcbs12ma");
$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'];

		$q = $dbh->query("SELECT sm.*, k.whitelist, k.awork_company_id 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(mb_convert_encoding($result['gcstarter'], 'ISO-8859-1', 'UTF-8'), true);
		$r = json_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 ($data != null && 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']}', 
									awork_company_id = '{$kunde['awork_company_id']}' 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]['aktiv'] = (string)$result[$i]['aktiv'];
			//$result[$i]['ort'] = mb_convert_encoding($r['ort'], 'ISO-8859-1', 'UTF-8');
		}
		break;
	case 'awork_projekte':
		$result = array();
		if (isset($_REQUEST['awork_company_id'])) {
			$q = $dbh->query("SELECT * FROM awork_projekte WHERE awork_company_id = '{$_REQUEST['awork_company_id']}'  
							  OR awork_project_id = 'a751120a-2f91-4110-a62d-f50b4769236e'
							  ORDER BY projekt_allgemein DESC, tracked_duration DESC");
			$result["projekte"] = $q->fetchAll(PDO::FETCH_ASSOC);
		} else {
			$q = $dbh->query("SELECT * FROM awork_projekte");
			$result["projekte"] = $q->fetchAll(PDO::FETCH_ASSOC);
		}
		
		$q = $dbh->query("SELECT DISTINCT awork_company_id, kunde_name FROM awork_projekte ORDER BY kunde_name");
		$result["kunden"] = $q->fetchAll(PDO::FETCH_ASSOC);
		break;

	case 'tickets':
		if ($data != null && 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);
		//print_r($result);
		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 WHERE aktiv = '1'");
		$result = $q->fetchAll(PDO::FETCH_ASSOC);
		break;
	case 'kommentar':
		$id = (isset($_REQUEST['id']) && $_REQUEST['id'] != "undefined") ? $_REQUEST['id'] : '';

		if ($data != null && count($data) > 0) {
			if ($id == '') {
				$now = date('Y-m-d H:i:s');
				$q = $dbh->exec("INSERT INTO status_kommentar (kunde, datum, start, benutzer, benutzer2, fehler, status, kommentar, awork_project_id, awork_task_id, cdate)
								 VALUES ('{$data['kunde']}','{$data['datum']}','{$data['start']}',
								 '{$data['benutzer']}','{$data['benutzer2']}','{$data['fehler']}','{$data['status']}',
								 '{$data['kommentar']}','{$data['awork_project_id']}','{$data['awork_task_id']}',
								 '{$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']}',
									awork_project_id = '{$data['awork_project_id']}', 
									awork_task_id = '{$data['awork_task_id']}'
								 WHERE id = '{$id}' ");
			}

			if (isset($data['awork_project_id']) && $data['awork_project_id'] != '' && $data['awork_task_id'] == '') {
				// create task in awork
				// save into $data['awork_task_id']
				exec('C:\dev\python3.11\python.exe C:\Projekte\Python\awork\awork_tasks.py ' . $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)" : "a.datum >= '{$_REQUEST['datum']}' ";
		$datum_bis = (!isset($_REQUEST['datum_bis']) || $_REQUEST['datum_bis'] == '') ? "a.datum > date_add(now(), INTERVAL -32 DAY)" : "a.datum <= '{$_REQUEST['datum_bis']}' ";
		$kunde = (!isset($_REQUEST['kunde']) || $_REQUEST['kunde'] == '') ? "1" : "k.kunde LIKE '%{$_REQUEST['kunde']}%'";

		$q = $dbh->query("SELECT DISTINCT a.datum, k.kunde, sm.aufgabe, k.awork_company_id, k.system, a.bundesland, a.feiertag, 
							k.start_soll, k.ende_soll, sm.start, sm.ende, sm.anzahl, ifnull(sk.awork_task_id, '') as awork_task_id,
							if(sk2.id IS NULL, sm.bearbeitet, '1') as bearbeitet,
								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() > date_add(concat(a.datum, ' ', k.ende_soll), INTERVAL 1 HOUR)), 1, 0) as fehler,
								if(sm.anzahl is null and now() > date_add(concat(a.datum, ' ', k.ende_soll), INTERVAL 1 HOUR), 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
							LEFT JOIN status_kommentar as sk2 ON a.datum = sk2.datum AND k.kunde = sk2.kunde

							WHERE {$datum} AND {$datum_bis} AND {$kunde} AND a.datum <= now() AND k.erster_status <= a.datum
							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); 
// print_r($result);
// echo json_last_error();