}
// 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!");
}
}
