} // Erzeugen eines Eintrages if (filter_input(INPUT_POST, 'action') == "insert-users-timetable") { $insArray['iUserID'] = filter_input(INPUT_POST, 'iUserID', FILTER_VALIDATE_INT); $insArray['Time_Start'] = filter_input(INPUT_POST, 'Time_Start', FILTER_SANITIZE_STRIPPED); $insArray['Time_End'] = filter_input(INPUT_POST, 'Time_End', FILTER_SANITIZE_STRIPPED); $insArray['Date'] = filter_input(INPUT_POST, 'Date', FILTER_SANITIZE_STRIPPED); $insArray['iProjekt'] = filter_input(INPUT_POST, 'iProjekt', FILTER_VALIDATE_INT); $insArray['iCat'] = filter_input(INPUT_POST, 'iCat', FILTER_VALIDATE_INT); foreach ($insArray as $key => $value) { if (is_null($value)) { exit_error("unvollständig!"); } } $query = 'INSERT INTO `zeiterfassung` ' . '(iUserID, Time_Start, Time_End, Date, iProjekt, iCat) Values ' . '(?, ?, ?, ?, ?, ?) '; $stmt = $tze->query($query, 'isssii', $insArray['iUserID'], $insArray['Time_Start'], $insArray['Time_End'], $insArray['Date'], $insArray['iProjekt'], $insArray['iCat']); if ($stmt) { $query = 'SELECT * FROM `ze_full` WHERE `id` = ?'; $rows = $tze->get_Results($query, "i", $stmt->insert_id); $message = json_encode(["Result" => "OK", "Record" => $rows[0]]); exit($message); } else { exit_error(); } } // Update eines Eintrages if (filter_input(INPUT_POST, 'action') == "update-users-timetable") { $updArray['id'] = filter_input(INPUT_POST, 'id', FILTER_VALIDATE_INT); $updArray['Time_Start'] = filter_input(INPUT_POST, 'Time_Start', FILTER_SANITIZE_STRIPPED); $updArray['Time_End'] = filter_input(INPUT_POST, 'Time_End', FILTER_SANITIZE_STRIPPED); $updArray['iProjekt'] = filter_input(INPUT_POST, 'iProjekt', FILTER_VALIDATE_INT);
$rows[] = $row; } exit(json_encode(array("Result" => "OK", "Records" => $rows))); } // ziehe Mitarbeiter-Liste für ein Projekt if ($action == 'projekt-get-users-attached' && $id > 0) { $strQuery = "SELECT `iUser`, `sVorname`, `sNachname`, `sLogin` FROM `pz_full` WHERE iProjekt = ?"; $rows = $tze->get_Results($strQuery, "i", $id); exit(json_encode(array("Result" => "OK", "Records" => $rows))); } // Setze Name für ein Projekt if ($action == 'projekt-set-name' && $id > 0) { $neuerName = filter_input(INPUT_POST, 'Projekt', FILTER_SANITIZE_STRIPPED); if (!is_null($neuerName)) { $strQuery = 'UPDATE projekte set Projekt = "' . $neuerName . '" WHERE id = ' . $id; if ($tze->query($strQuery)) { $message = ["Result" => "OK", "Record" => ["id" => $id, "Projekt" => $neuerName]]; exit(json_encode($message)); } else { exit_error("Name konnte nicht übernommen werden."); } } else { exit_error("Name darf nicht leer sein!"); } } // Füge neues Projekt ein if ($action == 'projekt-create') { $neuerName = filter_input(INPUT_POST, 'Projekt', FILTER_SANITIZE_STRIPPED); if ($neuerName !== false and !is_null($neuerName)) { $strQuery = 'INSERT INTO `projekte` (`Projekt`) VALUE (?)'; $request = $tze->query($strQuery, "s", $neuerName);
// } // } else { // exit(json_encode(array('Result' => "ERROR", "Message" => 'Fehler in ' . $key, "ValType" => $valtype))); // } // } else { $updateQuery .= '`' . $key . '` = "' . $value . '", '; // } } $userId = filter_input(INPUT_POST, 'userId', FILTER_VALIDATE_INT); if (!empty($userId)) { $newPassword = filter_input(INPUT_POST, 'sPassword_hash'); $forceNewPassword = filter_input(INPUT_POST, 'bForce_Update_PW', FILTER_VALIDATE_BOOLEAN); if (!empty($newPassword)) { $pw = Neues_Passwort(); $pw_string = password_hash($pw); if (!$tze->query('UPDATE ma set sPassword_hash = ?,`bForce_Update_PW` = 1 WHERE userId = ?', 'si', $pw_string, $userId)) { exit_error("Fehler bei PW-Update"); } } elseif (!empty($forceNewPassword)) { if (!$tze->query('UPDATE ma set `bForce_Update_PW` = 1 WHERE userId = ?', 'i', $userId)) { exit_error("Fehler bei PW-Update"); } } // $result = $tze->query($sql, $vals, $inputArray); $result = $tze->Result_nPrep('UPDATE ma set ' . substr($updateQuery, 0, -2) . ' WHERE userId = "' . $userId . '"'); if ($result) { $rows = $tze->get_Results('SELECT * from ma_view WHERE `userId` = ?', "i", $userId); $row = $rows[0]; $jTableResult = array('Result' => "OK", 'Record' => $row); //, "input" => $inputArray); if (isset($pw)) {
$ticketName = filter_var($tickettyp['ticketName'], FILTER_SANITIZE_STRING); $ticketId = $tickettyp['ticketId']; $ticketHead .= ", tickets.`{$ticketName}` \n"; $ticketQuery .= ", sum(if(ticketId = {$ticketId}, done_tickets, 0)) as `{$ticketName}` \n"; if ($tickettyp['counterSwitch'] > 1) { $counterName = filter_var($tickettyp['counterName'], FILTER_SANITIZE_STRING); $ticketHead .= ", tickets.`{$ticketName} - {$counterName}` \n"; $ticketQuery .= ", sum(if(ticketId = {$ticketId}, sum_counter, 0)) as `{$ticketName} - {$counterName}` \n"; } if ($tickettyp['durationSwitch'] > 1) { $ticketHead .= ", tickets.`{$ticketName} - Zeit`\n"; $ticketQuery .= ", sum(if(ticketId = {$ticketId}, sum_duration, 0)) as `{$ticketName} - Zeit`\n"; } } $query = "SELECT \n maId as `Kürzel`,\n sNachname AS Nachname,\n sVorname AS Vorname, \n sum_tickets.tickets as `Summe Tickets`\n {$ticketHead}\n FROM ma\n left join (\n SELECT \n userId,\n sum(done_tickets) as tickets\n FROM tickets_view \n WHERE `projektId` = ? and date = ?\n group by userId\n ) sum_tickets on (ma.userId = sum_tickets.userId)\n left join (\n SELECT \n userId\n {$ticketQuery}\n FROM tickets_view \n WHERE `projektId` = ? and date = ?\n group by userId\n ) tickets on (ma.userId = tickets.userId)\n WHERE deleted = 0 or deleted_date > ?\n order by sNachname"; $stmt = $tze->query($query, 'isiss', $projectId, $Date, $projectId, $Date, $Date); $headers = $tze->fetch_headers($stmt); $rows = $tze->fetch_assoc($stmt); //neues Blatt anlegen, Blatt benennen und Inhalt einfügen $worksheet = $objPHPExcel->createSheet()->setTitle($project["Projekt"])->fromArray($headers)->fromArray($rows, null, "A2"); $numRows = count($rows); $highestColumn = $worksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //e.g., 6 $firstRowStyle = ['font' => ['bold' => true], 'borders' => ['bottom' => ['style' => PHPExcel_Style_Border::BORDER_THIN]]]; $firstRowTicketStyle = ['font' => ['bold' => true], 'borders' => ['bottom' => ['style' => PHPExcel_Style_Border::BORDER_THIN]]]; $worksheet->getStyle('A1:' . $highestColumn . '1')->applyFromArray($firstRowStyle); $worksheet->getStyle('D1:' . $highestColumn . '1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setWrapText(true)->setTextRotation(90); $worksheet->getRowDimension('1')->setRowHeight("95"); $worksheet->getColumnDimension('A')->setAutoSize(true); $worksheet->getColumnDimension('B')->setAutoSize(true);
$args = ['code' => FILTER_VALIDATE_INT, 'ListLabel' => FILTER_SANITIZE_STRIPPED, 'ButtonLabel' => FILTER_SANITIZE_STRIPPED, 'class' => FILTER_SANITIZE_STRIPPED, 'position' => FILTER_VALIDATE_INT, 'usual' => FILTER_VALIDATE_INT, 'id' => FILTER_VALIDATE_INT]; $types = "isssiii"; $inputs = filter_input_array(INPUT_POST, $args); foreach ($inputs as $key => &$value) { if (empty($value)) { if ($key == 'usual') { $value = "0"; } elseif ($key == 'ListLabel' || $key == 'ButtonLabel' || $key == 'class') { $value = ""; } else { exit_error('Angabe fehlt!'); } } } $updateQuery = 'update dispoCodes ' . 'set `code` = ?, `ListLabel` = ?, `ButtonLabel` = ?, ' . '`class` = ?, `position` = ?, `usual` = ? ' . 'where `id` = ?'; $update = $tze->query($updateQuery, $types, $inputs); if ($update) { $rows = $tze->get_Results("select * from dispoCodes where id = ?", 'i', $inputs['id']); $json_data = json_encode(["Result" => "OK", "Record" => $rows[0]]); exit($json_data); } } if (filter_input(INPUT_POST, 'action') == "insert") { $args = ['code' => FILTER_VALIDATE_INT, 'ListLabel' => FILTER_SANITIZE_STRIPPED, 'ButtonLabel' => FILTER_SANITIZE_STRIPPED, 'class' => FILTER_SANITIZE_STRIPPED, 'position' => FILTER_VALIDATE_INT, 'usual' => FILTER_VALIDATE_INT]; $types = "isssii"; $inputs = filter_input_array(INPUT_POST, $args); foreach ($inputs as $key => &$value) { if (empty($value)) { if ($key == 'usual') { $value = "0"; } elseif ($key == 'ListLabel' || $key == 'ButtonLabel' || $key == 'class') {
if ($ticketId !== null && $ticketId !== false) { $project_result = $tze->get_Results('Select * from projekte_tickets where ticketId = ?', 'i', $ticketId); $intTest = ['ticketNumber', 'counter', 'duration']; foreach ($intTest as $testele) { if ($project_result['0'][$testele . 'Switch'] > 2) { if (is_null(${$testele}) || ${$testele} == false) { exit_error('Angabe fehlt!', null, null, ["errorIn" => $testele]); } } } $boolTest = ['undone']; foreach ($boolTest as $testele) { if ($project_result['0'][$testele . 'Switch'] > 1) { if (is_null(${$testele})) { exit_error('Angabe fehlt!', null, null, ["errorIn" => $testele]); } } } $ticketTable = 'tickets'; //_' . $project_result['0']['projektId']; $insertQuery = "insert into {$ticketTable} (userId, date, ticketId, ticketNumber, duration, counter, undone) value (?, ?, ?, ?, ?, ?, ?)"; if ($tze->query($insertQuery, 'isiiiis', $_SESSION['userId'], $strDate, $ticketId, $ticketNumber, $duration, $counter, $undone)) { $query = "select " . "done_tickets as tickets, " . "sum_counter as counter, " . "sum_duration as duration, " . "timestamp " . "from tickets_view " . "where userId = ? and date = ? and ticketId = ?"; $rows = $tze->get_Results($query, "isi", $_SESSION['userId'], $strDate, $ticketId); $message = ["Result" => "OK", "data" => $rows[0], "undone" => $undone]; exit(json_encode($message)); } } } } exit_error('Ungültiger Aufruf!', $oDatenbank);
# if (!$_SESSION['iAdmin'] == 1) { exit_error('unzureichende Rechte!'); } else { $Date = filter_input(INPUT_POST, 'Date', FILTER_SANITIZE_STRIPPED); $Date = $tze->sqlDate($Date); $objPHPExcel = new PHPExcel(); $dispoCodesArray = $tze->get_Results("select * from dispoCodes"); foreach ($dispoCodesArray as $code) { $dispoCodes .= 'sum( if (icat = ' . $code['id'] . ', time_to_sec(Duration), 0)) / 24 / 60 / 60 as `' . $code['code'] . ". " . $code['ButtonLabel'] . '`, '; $dispoHeaders .= 'zeiten.`' . $code['code'] . ". " . $code['ButtonLabel'] . '`, '; } $dispoCodes = substr($dispoCodes, 0, -2); $dispoHeaders = substr($dispoHeaders, 0, -2); $query = "\n SELECT \n maId as `Kürzel`,\n sNachname AS Nachname,\n sVorname AS Vorname,\n iArbeitszeit AS `Arbeitszeit Soll`,\n zeiten.anfa as `First IN`,\n zeiten.ende as `Last OUT`,\n zeiten.az as `Gesamt exkl. Pause`,\n {$dispoHeaders}\n FROM ma \n LEFT JOIN (\n SELECT \n iUserID, \n (time_to_sec(min(Time_Start)) - time_to_sec(Date))/24/60/60 as anfa,\n (time_to_sec(max(Time_End)) - time_to_sec(Date))/24/60/60 as ende,\n sum(if(class = 'Pause',0,time_to_sec(Duration)))/24/60/60 as az,\n {$dispoCodes}\n FROM ze_full \n WHERE DATE = ? group by iUserID\n ) zeiten ON (ma.userId = zeiten.iUserID)\n WHERE deleted = 0 or deleted_date > ?\n order by sNachname"; $stmt = $tze->query($query, 'ss', $Date, $Date); // $headers = $tze->fetch_headers($stmt); $rows = $tze->fetch_assoc($stmt); $numRows = count($rows); $worksheet = $objPHPExcel->getActiveSheet(); $worksheet->setTitle("Zusammenfassung")->fromArray($headers)->fromArray($rows, null, "A2"); $highestColumn = $worksheet->getHighestColumn(); $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //e.g., 6 $firstRowStyle = ['font' => ['bold' => true], 'borders' => ['bottom' => ['style' => PHPExcel_Style_Border::BORDER_THIN]]]; $worksheet->getStyle('E2:' . $highestColumn . ($numRows + 1))->getNumberFormat()->setFormatCode('hh:mm:ss'); $worksheet->getStyle('A1:' . $highestColumn . '1')->applyFromArray($firstRowStyle); $worksheet->getColumnDimension('A')->setAutoSize(true); $worksheet->getColumnDimension('B')->setAutoSize(true); $worksheet->getColumnDimension('C')->setAutoSize(true);
} if ($passwd_new_1 != $passwd_new_2) { $return = "ERROR"; $ErrorFields[] = ["Field" => "passwd_new_2", "Message" => "Passworter stimmen nicht überein!"]; } if (!password_verify($passwd_old, $sOldPassword_hash)) { $return = "ERROR"; $ErrorFields[] = ["Field" => "passwd_old", "Message" => "Passwort ist falsch!"]; } if (password_verify($passwd_new_1, $sOldPassword_hash)) { $return = "ERROR"; $ErrorFields[] = ["Field" => "passwd_new_1", "Message" => "Darf nicht altem Passwort identisch sein!"]; } if ($return == "OK") { $sNewPassword_hash = password_hash($passwd_new_1, PASSWORD_DEFAULT); if ($tze->query("UPDATE ma set sPassword_hash = ?, bForce_Update_PW = '0' WHERE userId = ?", "si", $sNewPassword_hash, $iPasswordUserID)) { $_SESSION["bForce_Update_PW"] = 0; $message = ["Result" => $return, "Message" => "Passwort erfolgreich geändert.", "GoTo" => "."]; } else { $message = ["Result" => "ERROR", "Message" => "Passwort konnte nicht gespeichert werden!"]; } } else { $message = ["Result" => $return, "ErrorFields" => $ErrorFields]; } exit(json_encode($message)); } ?> <!DOCTYPE html> <!-- Copyright (c) 2015, Johannes Boost <jjjb at usw-tools.de>
$iLastProj = $row['iProjekt']; $iLastCat = $row['iCat']; } } # Zeitstempel setzen if ($_SERVER['REQUEST_METHOD'] == 'POST') { if (filter_input(INPUT_POST, 'action') == "TimeStamp") { $projectId = filter_input(INPUT_POST, 'ProjektID', FILTER_VALIDATE_INT); $timeClassId = filter_input(INPUT_POST, 'TimeClassID', FILTER_VALIDATE_INT); if (is_int($projectId) and is_int($timeClassId)) { if ($timeClassId != $iLastCat or $projectId != $iLastProj) { $old = []; $new = []; if (isset($strID)) { $StampEnd = 'UPDATE `o2bo`.`zeiterfassung` ' . 'SET `Time_End` = ? ' . 'WHERE `id` = ?'; $stmt = $tze->query($StampEnd, 'si', $strTime, $strID); if ($stmt) { $old = $tze->get_Results('Select * from `o2bo`.`ze_full` WHERE `id` = ?', 'i', $strID); } else { exit_error("Fehler beim setzen von ID ' . {$strID} . '!"); } } if ($timeClassId > 0) { $StampStart = 'INSERT INTO `zeiterfassung` ' . '(`iUserID`, `Date`, `Time_Start`, `iProjekt`, `iCat`) ' . 'VALUES (?,?,?,?,?)'; $stmt = $tze->query($StampStart, 'issii', $iUserID, $strDate, $strTime, $projectId, $_POST['TimeClassID']); if ($stmt) { $new = $tze->get_Results('Select * from `ze_full` WHERE `id` = ?', 'i', $stmt->insert_id); } else { exit_error("Fehler beim setzen des neuen Eintrags!"); } }