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