}
$strQueryFilter .= " iProjekt > 0 ";
} else {
if (!empty($nameFilter)) {
$strQueryFilter = " where {$nameFilter} ";
} else {
$strQueryFilter = "";
}
}
}
$sort = filter_input(INPUT_POST, 'jtSorting', FILTER_SANITIZE_STRIPPED);
if (empty($sort)) {
$sort = "sNachname ASC";
}
$strQuerySort = ' order by ' . $sort;
$rows = $tze->get_Results($strQuery . $strQueryFilter . $strQuerySort, "sssss", $Date, $Date, $Date, $Date, $Date);
if (count($rows) > 0) {
$last = max(array_column($rows, "timestamp"));
}
if (empty($last)) {
$last = "0000-00-00 00:00:00";
}
$message = json_encode(["Result" => "OK", "Records" => $rows, "last" => $last]);
exit($message);
}
if (filter_input(INPUT_POST, 'action') == 'get-users-update') {
$Date = filter_input(INPUT_POST, 'Date', FILTER_SANITIZE_STRIPPED);
$Date = $tze->sqlDate($Date);
$strQuery = 'select
userId as iUserID, sVorname, sNachname, iArbeitszeit,
sec_to_time(iArbeitszeit * 60 * 60) as Arbeitszeit,
exit_error('unzureichende Rechte!');
} else {
// ziehe Projekte Liste
if ($action == 'projekt-list') {
$strQuery = "SELECT id, Projekt FROM `projekte` WHERE id > 0 AND deleted != 1";
$request = $oDatenbank->query($strQuery);
$rows = array();
while ($row = $request->fetch_assoc()) {
$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!");
if (!empty($jtSorting)) {
$orderBy = " Order By {$jtSorting} ";
} else {
$orderBy = "";
}
$action = filter_input(INPUT_POST, 'action');
// Mitarbeiter Auflisten
if ($action == "list") {
$nameFilter = filter_input(INPUT_POST, 'nameFilter', FILTER_SANITIZE_STRIPPED);
if (empty($nameFilter)) {
$nameFilter = '';
} else {
$nameFilter = "where `maId` like '%{$nameFilter}%' or `sVorname` like '%{$nameFilter}%' or `sNachname` like '%{$nameFilter}%' or `sLogin` like '%{$nameFilter}%' ";
}
//Get record count
$recordCounts = $tze->get_Results("SELECT COUNT(sLogin) AS RecordCount FROM ma_view {$nameFilter}");
$recordCount = $recordCounts[0]['RecordCount'];
$queryString = "SELECT * FROM ma_view {$nameFilter} {$orderBy} {$limit}";
$rows = $tze->get_Results($queryString);
// $rows = array();
// while ($row = $result->fetch_assoc()) {
// $rows[] = $row;
// }
//Return result to jTable
$jTableResult = ['Result' => "OK", 'Records' => $rows, 'TotalRecordCount' => $recordCount];
print json_encode($jTableResult);
exit;
}
// Mitarbeitern updaten
if ($action == "update") {
# Werte einlesen
if (filter_input(INPUT_POST, 'mode') == "admin") {
#
# Admin Status Prüfen
#
if (!$_SESSION['iAdmin'] == 1) {
exit_error('unzureichende Rechte!');
} else {
// Datum einlesen
$Date = filter_input(INPUT_POST, 'Date', FILTER_SANITIZE_STRIPPED);
// Datum SQL passend formatieren
$Date = $tze->sqlDate($Date);
// Arbeitsmappe erstellen
$objPHPExcel = new PHPExcel();
//Projekte einlesen
$query = 'select * from projekte where deleted != 1';
$projects = $tze->get_Results($query);
//jedes Projekt einzeln
foreach ($projects as $project) {
$projectId = $project["id"];
$queryTicketTypes = "select * from projekte_tickets where projektId = ?";
$ticketTypes = $tze->get_Results($queryTicketTypes, "i", $projectId);
$ticketHead = "";
$ticketQuery = "";
foreach ($ticketTypes as $tickettyp) {
$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";
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
if (filter_input(INPUT_POST, 'mode') == "admin") {
#
# Admin Status Prüfen
#
if (!$_SESSION['iAdmin'] == 1) {
exit_error('unzureichende Rechte!');
} else {
if (filter_input(INPUT_POST, 'action') == "list") {
$jtSorting = filter_input(INPUT_POST, 'jtSorting', FILTER_SANITIZE_STRIPPED);
if (!empty($jtSorting)) {
$sort = "Order by {$jtSorting}";
} else {
$sort = '';
}
$rows = $tze->get_Results("select * from dispoCodes where deleted = false {$sort}");
$json_data = json_encode(["Result" => "OK", "Records" => $rows]);
exit($json_data);
}
if (filter_input(INPUT_POST, 'action') == "update") {
$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!');
$jtPageSize = filter_input(INPUT_POST, 'jtPageSize', FILTER_SANITIZE_NUMBER_INT);
$jtSorting = filter_input(INPUT_POST, 'jtSorting', FILTER_SANITIZE_STRIPPED);
if (!empty($jtPageSize)) {
$jtStartIndex = !empty($jtPageSize) ? $jtStartIndex : 0;
$limit = " LIMIT {$jtStartIndex}, {$jtPageSize} ";
} else {
$limit = "";
}
if (!empty($jtSorting)) {
$orderBy = " Order By {$jtSorting} ";
} else {
$orderBy = "";
}
# letzen, nicht beendeten Tag abrufen
$QueryLastDay = 'SELECT Date FROM `ze_full` WHERE iUserID = ? and Time_End = "0000-00-00 00:00:00"';
$LastLogedTime = $tze->get_Results($QueryLastDay, "i", $_SESSION['userId']);
if (count($LastLogedTime) > 0) {
// if (!empty($LastLogedTime[0]['Date'])) {
$strDate = $LastLogedTime[0]['Date'];
} else {
$strDate = date("Y-m-d");
}
$action = filter_input(INPUT_POST, 'action');
$mode = filter_input(INPUT_POST, 'mode');
if ($mode == "admin") {
#
# Admin Status Prüfen
#
if (!$_SESSION['iAdmin'] == 1) {
exit_error('unzureichende Rechte!');
} else {
require_once __DIR__ . '/../../includes/functions.inc';
require_once __DIR__ . '/../../includes/PHPExcel.php';
$tze = new tze();
$oDatenbank = $tze->mysql();
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
if (filter_input(INPUT_POST, 'mode') == "admin") {
#
# Admin Status Prüfen
#
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();
# Namensnennung - Nicht-kommerziell - Weitergabe unter gleichen Bedingungen
# http://creativecommons.org/licenses/by-nc-sa/3.0/de/legalcode
#
$strSubSiteName = 'Passwort';
require_once __DIR__ . '/../includes/auth.inc';
require_once __DIR__ . '/../includes/functions.inc';
require_once __DIR__ . '/../includes/passwordLib.inc';
$tze = new tze();
$oDB = $tze->mysql();
$sError = "";
$sIntro = "";
if ($_SESSION["bForce_Update_PW"]) {
$sIntro = "<p>Das Passwort muss geändert werden!</p>\n";
}
$iPasswordUserID = $_SESSION['userId'];
$result = $tze->get_Results('SELECT * FROM ma WHERE userId = ?', 'i', $iPasswordUserID);
if ($result) {
if (count($result) == 1) {
$sUserName = $result[0]["sLogin"] . " (" . $result[0]['sVorname'] . " " . $result[0]['sNachname'] . ")";
$sOldPassword_hash = $result[0]["sPassword_hash"];
} else {
$sUserName = "******"font-size: smaller; color: #800000;\">User nicht gefunden!</span>\n";
}
}
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$passwd_old = filter_input(INPUT_POST, 'passwd_old', FILTER_SANITIZE_STRIPPED);
$passwd_new_1 = filter_input(INPUT_POST, 'passwd_new_1', FILTER_SANITIZE_STRIPPED);
$passwd_new_2 = filter_input(INPUT_POST, 'passwd_new_2', FILTER_SANITIZE_STRIPPED);
$return = "OK";
# Falls alles korrekt gefüllt, Passwort auf Änderung prüfen
if ($passwd_new_1 == "") {
}
}
# 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!");
}
}
$rows = array_merge($old, $new);
exit(json_encode(array('Result' => 'OK', 'Data' => $rows)));
