<?php
if (isset($_POST['mitarbeiterdb'])) {
$user = $_POST['mitarbeiterdb'];
$ergebnis = mysqli_query($mysqli, "SELECT id FROM user WHERE name='".$user."';");
$row = mysqli_fetch_object($ergebnis);
$userid = $row->id;
}
if (isset($_POST['filter_taetigkeit'])) {
$taetid = $_POST['filter_taetigkeit'];
}
if (isset($_POST['filter_product'])) {
$prodid = $_POST['filter_product'];
}
if (isset($_POST['filterName'])) { // lese DB-Inhalt für einen bestimmten Mitarbeiter aus (siehe u.id)
$db_erg = mysqli_query($mysqli, "SELECT
pr.id AS prID,
t.taetigkeit AS Taetigkeit,
p.produkt AS Produkt,
t.id AS tID,
p.id AS pID,
pr.beschreibung AS Beschreibung,
u.name AS User,
z.zeit AS Zeit,
z.datum AS Datum
FROM
zeiten AS z,
projekte AS pr,
user AS u,
taetigkeiten AS t,
produkte AS p
WHERE
z.datum >= '".$wochenanfang."' AND z.datum <= '".$wochenende."' AND u.name = '".$user."' AND u.id = z.userid AND pr.id = z.taetprodid AND pr.taetigkeit = t.id AND pr.produkt = p.id
ORDER BY
z.datum ASC;");
} elseif (isset($_POST['filterTaet'])) { // lese DB-Inhalt für eine bestimmte Tätigkeit aus
$db_erg = mysqli_query($mysqli, "SELECT
pr.id AS prID,
t.taetigkeit AS Taetigkeit,
p.produkt AS Produkt,
t.id AS tID,
p.id AS pID,
pr.beschreibung AS Beschreibung,
u.name AS User,
z.zeit AS Zeit,
z.datum AS Datum
FROM
zeiten AS z,
projekte AS pr,
user AS u,
taetigkeiten AS t,
produkte AS p
WHERE
z.datum >= '".$wochenanfang."' AND z.datum <= '".$wochenende."' AND t.id = '".$taetid."' AND pr.taetigkeit = '".$taetid."' AND p.id = '".$prodid."' AND z.taetprodid = pr.id
ORDER BY
z.datum ASC;");
} elseif (isset($_POST['filterProd'])) { // lese DB-Inhalt für ein bestimmtes Produkt aus
$db_erg = mysqli_query($mysqli, "SELECT
pr.id AS prID,
t.taetigkeit AS Taetigkeit,
p.produkt AS Produkt,
t.id AS tID,
p.id AS pID,
pr.beschreibung AS Beschreibung,
u.name AS User,
z.zeit AS Zeit,
z.datum AS Datum
FROM
zeiten AS z,
projekte AS pr,
user AS u,
taetigkeiten AS t,
produkte AS p
WHERE
z.datum >= '".$wochenanfang."' AND z.datum <= '".$wochenende."' AND p.id = '".$prodid."' AND pr.produkt = '".$prodid."' AND t.id = '".$taetid."' AND z.taetprodid = pr.id
ORDER BY
z.datum ASC;");
} if (isset($_POST['kombination'])) { // nach Kombination filtern
$db_erg = mysqli_query($mysqli, "SELECT
pr.id AS prID,
t.taetigkeit AS Taetigkeit,
p.produkt AS Produkt,
t.id AS tID,
p.id AS pID,
pr.beschreibung AS Beschreibung,
u.name AS User,
z.zeit AS Zeit,
z.datum AS Datum
FROM
zeiten AS z,
projekte AS pr,
user AS u,
taetigkeiten AS t,
produkte AS p
WHERE
z.datum >= '".$wochenanfang."' AND z.datum <= '".$wochenende."' AND u.id = '".$userid."' AND pr.taetigkeit = '".$taetid."' AND t.id = '".$taetid."' AND pr.produkt = '".$prodid."' AND p.id = '".$prodid."' AND pr.id = z.taetprodid
ORDER BY
z.datum ASC;");
} elseif (isset($_POST['auswertenall'])) { // lese gesamten DB-Inhalt aus
$db_erg = mysqli_query($mysqli, "SELECT
pr.id AS prID,
t.taetigkeit AS Taetigkeit,
p.produkt AS Produkt,
t.id AS tID,
p.id AS pID,
pr.beschreibung AS Beschreibung,
u.name AS User,
z.zeit AS Zeit,
z.datum AS Datum
FROM
zeiten AS z,
projekte AS pr,
user AS u,
taetigkeiten AS t,
produkte AS p
WHERE
z.datum >= '".$wochenanfang."' AND z.datum <= '".$wochenende."' AND pr.id = z.taetprodid AND pr.taetigkeit = t.id AND pr.produkt = p.id
ORDER BY
z.datum ASC;");
}
if (!$db_erg) {
die ('Ungültige Abfrage: '.$mysqli->error);
}
echo '<table>';
echo ' <tr>';
echo ' <td colspan="11" class="head_cap">';
echo ' <table style="width:100%; border:none; background-color:#A9F5BC;"><tr>';
echo ' <td style="border:none" align="left"><input type="submit" value="<<" name="prevWeek" class="switch"></td>';
echo ' <td style="width:90%; border:none; text-align:center">';
if (date('Y', $wochenanfang) <> date('Y', $wochenende)) {
echo ' Kalenderwoche '.$kw.': '.date('j.m.', $wochenanfang).' - '.date('j.m.Y', $wochenende);
} else {
echo ' Kalenderwoche '.$kw.': '.date('j.m.Y', $wochenanfang).' - '.date('j.m.Y', $wochenende);
}
echo ' <input type="hidden" value="'.$wochenanfang.'" name="wa">';
echo ' <input type="hidden" value="'.$wochenende.'" name="we">';
echo ' </td>';
echo ' <td style="border:none" align="right"><input type="submit" value=">>" name="nextWeek" class="switch"></td>';
echo ' </tr></table>';
echo ' </td>';
echo ' </tr>';
echo ' <tr>';
echo ' <th>Mitarbeiter</th>
<th>Tätigkeit</th>
<th>Produkt</th>
<th>Beschreibung</th>
<th width="150">MO</th>
<th width="150">DI</th>
<th width="150">MI</th>
<th width="150">DO</th>
<th width="150">FR</th>
<th width="150">SA</th>
<th width="150">SO</th>';
echo ' <th>ID</th>';
echo ' </tr>';
$name = array();
$row_coll = array();
$user_table = mysqli_query($mysqli, "SELECT id, name FROM user;");
$anzahl = mysqli_num_rows($user_table);
while ($row = mysqli_fetch_assoc($user_table)) {
if (!isset($row_coll[$row['id']])) {
$row_coll[$row['id']] = array();
$row_coll[$row['id']]['id'] = $row['id'];
$row_coll[$row['id']]['name'] = $row['name'];
}
}
$j = 0;
foreach ($row_coll as $ma_id => $id) {
$td = array();
$td[0] = $row_coll[$ma_id]['id'];
$td[1] = $row_coll[$ma_id]['name'];
for ($i = 0; $i < $anzahl; $i++) {
$name[$j] = $td[$i];
$j++;
}
}
$row_collector = array();
if (mysqli_num_rows($db_erg)) {
while ($row = mysqli_fetch_assoc($db_erg)) {
if (!isset($row_collector[$row['tID']])) {
$row_collector[$row['tID']] = array();
$row_collector[$row['tID']]['taet'] = $row['Taetigkeit'];
$row_collector[$row['tID']]['user'] = $row['User'];
$row_collector[$row['tID']]['data'] = array();
}
if (!isset($row_collector[$row['tID']]['data'][$row['pID']])) {
$row_collector[$row['tID']]['data'][$row['pID']] = array();
$row_collector[$row['tID']]['data'][$row['pID']]['prod'] = $row['Produkt'];
$row_collector[$row['tID']]['data'][$row['pID']]['desc'] = $row['Beschreibung'];
$row_collector[$row['tID']]['data'][$row['pID']]['user'] = $row['User'];
$row_collector[$row['tID']]['data'][$row['pID']]['proj'] = $row['prID'];
$row_collector[$row['tID']]['data'][$row['pID']]['time'] = array();
}
$row_collector[$row['tID']]['data'][$row['pID']]['time'][$row['Datum']] = $row['Zeit'];
}
foreach ($row_collector as $taet_id => $teat) {
$td = array();
$td[1] = $row_collector[$taet_id]['taet'];
foreach ($row_collector[$taet_id]['data'] as $prod_id => $prod) {
$td[0] = $row_collector[$taet_id]['data'][$prod_id]['user'];
$td[2] = $row_collector[$taet_id]['data'][$prod_id]['prod'];
$td[3] = $row_collector[$taet_id]['data'][$prod_id]['desc'];
$td[11] = $row_collector[$taet_id]['data'][$prod_id]['proj'];
$user_id = "SELECT userid FROM projekte WHERE id = '".$td[11]."';";
$res = mysqli_query($mysqli, $user_id);
$row = mysqli_fetch_assoc($res);
for ($j = 0; $j < count($name); $j++) {
if ($name[$j] == $row['userid']) {
$td[0] = $name[$j+1];
}
}
for ($i = 0; $i < 7; $i++) {
$ts = $wochenanfang + ($i * 60 * 60 * 24);
if (isset($row_collector[$taet_id]['data'][$prod_id]['time'][$ts])) {
$td[$i+4] = $row_collector[$taet_id]['data'][$prod_id]['time'][$ts];
} else {
$td[$i+4] = ' ';
}
}
echo ' <tr>';
for ($i = 0; $i < count($td); $i++) {
echo ' <td>'.$td[$i].'</td>';
}
echo ' </tr>';
}
}
} else {
echo ' <tr><td colspan="11" style="text-align:center">Keine Einträge gefunden.</td></tr>';
}
echo '</table>';
echo '<br>Alle Einträge anzeigen für folgende(s/n) ...<br>';
echo '<br><input type="submit" style="height:24px; width:100px" name="filterTaet" value="Tätigkeit >"> ';
$taetigkeiten = array();
$res_t = mysqli_query($mysqli, "SELECT * FROM taetigkeiten") or die ("Get Taetigkeiten fehlgeschlagen.<br>".mysqli_error());
if (mysqli_num_rows($res_t)) {
while ($row_t = mysqli_fetch_assoc($res_t)) {
$taetigkeiten[count($taetigkeiten)] = $row_t;
}
}
echo '<tr><td><select name="filter_taetigkeit">';
foreach ($taetigkeiten as $taetigkeit) {
echo '<option value="'.$taetigkeit['id'].'">'.$taetigkeit['taetigkeit'].'</option>';
}
echo '</select></td><br><br>';
echo '<input type="submit" style="height:24px; width:100px" name="filterProd" value="Produkt >"> ';
$products = array();
$res_p = mysqli_query($mysqli, "SELECT * FROM produkte") or die ("Get Produkte fehlgeschlagen.<br>".mysqli_error());
if (mysqli_num_rows($res_p)) {
while ($row_p = mysqli_fetch_assoc($res_p)) {
$products[count($products)] = $row_p;
}
}
echo '<td><select name="filter_product">';
foreach ($products as $product) {
echo '<option value="'.$product['id'].'">'.$product['produkt'].'</option>';
}
echo '</select></td><br><br>';
echo '<input type="submit" style="height:24px; width:100px" name="filterName" value="Mitarbeiter >"> <input name="mitarbeiterdb" type="text" size="30" maxlength="30"><br><br>';
echo '<input type="submit" style="height:24px; width:140px" name="kombination" value="^^ Kombination"><br><br><br><br>';
echo '<input type="submit" name="btnOutputCancel" value="Zurück zur Eingabe"><br><br>';
echo '<input type="submit" name="export" value="Exportiere MySQL Daten zu Excel File">';
Eine andere Erklärung finde ich nicht. Aber irgendwie so muss es scheitern?!
