<?php
/**
* @author Bettina Schwarzer, Fritz-Haber-Institut
* @copyright 05/2012
*
* Liste Funktionen ohne Berechtigung fuer Aufruf aus Intranet:
*
* $_GET["s"] = 1, ='suchen', muss gesetzt sein
* $_GET["l"] = b, (fkt_listb in tab 'Funktion') zusätzlich liefert die Liste der Beauftragten FHI
*
*/
error_reporting(E_ALL ^ E_NOTICE);
if (!isset($_GET["s"]))
{
session_start();
if (! isset($_SESSION["userid"]))
{
include_once ($_SERVER['DOCUMENT_ROOT'] ."/fhiiqm/inc/func_lib.inc.php");
login($_SERVER["PHP_SELF"]);
exit;
}
if (is_null($_SESSION["recht"]) || (!is_null($_SESSION["recht"]) && !in_array("fmr",$_SESSION["recht"]) && !in_array("fme",$_SESSION["recht"])))
{
header("Location: start.php");
exit;
}
}
include_once($_SERVER['DOCUMENT_ROOT'] ."/fhiiqm/inc/dbconnect.inc.php");
if (!isset($dbc) || !$dbc) $dbc = new dbconnection();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<link href="/fhiiqm/css/db.css" rel="STYLESHEET" type="TEXT/CSS" media="screen"/>
<link href="/fhiiqm/css/db_print.css" rel="STYLESHEET" type="TEXT/CSS" media="print"/>
<title>Beauftragte</title>
</head>
<body>
<?php
if (!isset($_GET["s"]))
{
echo " ";
echo "<div class=\"float-br smaller\" valign='top'>";
echo " user: " . $_SESSION["userid"];
echo " <a class='sc' href='/fhiiqm/logout.php' title='Session beenden'>logout</a></div>\n";
echo "<div class=\"float-r\"><img src=\"/fhiiqm/img/reseller_account_template.png\" border=\"0\" alt=\"Mitarbeiter-Funktion\" title=\"Mitarbeiter-Funktion\"/></div>\n";
}
$rag = $_POST["rag"]; // Parameter aus Form
$search = $_POST["search"];
if (!$search) $search = $_GET["s"];
if (!$zeil) $zeil = 9999; // Anzahl der gezeigten Zeilen
$start = $_GET["st"]; // Start bei DS $start+1
if (!$start) $start=0;
if (!is_array($rag))
{
$rag["fktg"] = $_GET["g"]; // Funktionsgruppe
$rag["fkt"] = $_GET["f"]; // Funktion
$rag["nfkt"] = $_GET["n"]; // nicht ausgewaehlte Funktion
$rag["fktb"] = $_GET["b"]; // Geltungsbereich
$rag["bind"] = $_GET["j"]; // Verknuepfung
if ($_GET['s']) $bl = $_GET['l']; // Beauftragte Institut (Tabelle 'Funktion': fkt_listb ='b')
}
/*
$_GET["f"] = $rag["fkt"];
$_GET["b"] = $rag["fktb"];
$_GET["j"] = $rag["bind"];
*/
if ($search && !isset($_GET["s"]))
echo "<br /><br /> <a href=\"" . $_SERVER["PHP_SELF"] . "\" target=\"_self\" title=\"Gefahren\" class=\"sc\">neuer Bericht Beauftragte</a></p>\n";
if ($bl) $text = " am Fritz-Haber-Institut"; else $text="";
echo "<div align='center'>\n";
echo "<p><b>Bericht Beauftragte$text</b></p>\n";
echo "</div>\n";
if (!$search)
{
?>
<div align="center">
<h3><font face="Verdana, Arial, Helvetica, sans-serif"></font></h3>
<p>Bitte Kriterien für die Suche wählen</p>
</div>
<form action="<?php $_SERVER['PHP_SELF'] ?>" method="post" enctype="application/x-www-form-urlencoded" name="form_rag" target="_self">
<div align="center">
<table width="50%" border="0" cellspacing="3" cellpadding="3">
<tr>
<td>Funktionsgruppe</td>
<td>
<select name="rag[fktg]" size="1" onchange="this.form.submit();">
<?php
if ($rag["fktg"] == -1) $select = " selected "; else $select = "";
echo "<option $select value=\"-1\"></option>\n";
$sql = "SELECT fktgr_ID,fktgr_bez
FROM Funktion_Gruppe ORDER BY 1";
if ($result = $dbc->queryObjectArray($sql))
{
foreach ($result as $row)
{
if ($row->fktgr_ID == $rag['fktg'])
echo "<option selected value=\"$row->fktgr_ID\">$row->fktgr_bez</option>\n";
else
echo "<option value=\"$row->fktgr_ID\">$row->fktgr_bez</option>\n";
}
}
?>
</select>
</td>
</tr>
<tr>
<td>Funktion</td>
<td>
<select name="rag[fkt]" size="1">
<?php
if ($rag["fkt"] == -1) $select = " selected "; else $select = "";
echo "<option $select value=\"-1\"></option>\n";
if (isset($rag["fktg"]) && $rag["fktg"]) $where = " WHERE fktgr_ID = '".$rag["fktg"] ."'"; else $where ="";
$sql = "SELECT DISTINCT mf.fkt_ID,fkt_bez
FROM fhiiqm.MA_Funktion mf INNER JOIN Funktion f ON mf.fkt_ID=f.fkt_ID $where ORDER BY 2";
if ($result = $dbc->queryObjectArray($sql))
{
foreach ($result as $row)
{
if ($row->fkt_ID == $rag['fkt'])
echo "<option selected value=\"$row->fkt_ID\">$row->fkt_bez</option>\n";
else
echo "<option value=\"$row->fkt_ID\">$row->fkt_bez</option>\n";
}
}
?>
</select>
diese nicht <input type="checkbox" name="rag[nfkt]" value="1"/>
</td>
</tr>
<tr>
<td>Geltungsbereich</td>
<td>
<select name="rag[fktb]" size="1">
<?php
if ($rag["fktb"] == -1) $select = " selected "; else $select = "";
echo "<option $select value=\"-1\"></option>\n";
$sql = "SELECT DISTINCT fkt_bereich FROM fhiiqm.MA_Funktion ORDER BY 1";
if ($result = $dbc->queryObjectArray($sql))
{
foreach ($result as $row)
{
if ($row->fkt_bereich == $rag['fktb'])
echo "<option selected value=\"$row->fkt_bereich\">$row->fkt_bereich</option>\n";
else
echo "<option value=\"$row->fkt_bereich\">$row->fkt_bereich</option>\n";
}
}
?>
</select>
</td>
</tr>
<tr>
<td>Verknüpfung</td>
<td>UND
<input type="radio" name="rag[bind]" value="AND" <?php if (isset($rag["bind"]) && $rag["bind"]=="AND") echo "checked"; else echo "checked";?>>
ODER
<input type="radio" name="rag[bind]" value="OR" <?php if (isset($rag["bind"]) && $rag["bind"]=="OR") echo "checked"; ?>></td>
</tr>
<tr>
<td> </td>
<td><input class="button" type="submit" name="search" value="Bericht erstellen"/></td>
</tr>
</table>
</div>
</form>
<?php
}
else
{
echo "<div align='center'>\n";
//print_r($rag); echo "<br />";
$sql = "SELECT nachname,vorname,titel,abt_name,mf.fkt_ID,fkt_bez,fkt_bereich,fkt_bem,telefon_nr AS tel,email,geb_ID,raum_nr,mf_ID
FROM fhiiqm.MA_Funktion mf INNER JOIN Mitarbeiter m ON mf.persknr=m.Persknr
INNER JOIN Funktion f ON mf.fkt_ID=f.fkt_ID
LEFT OUTER JOIN Abteilung a ON m.abt_tel=a.abt_ID
LEFT OUTER JOIN Mitarbeiter_Raum mr ON m.persknr=mr.persknr
LEFT OUTER JOIN Raum r ON mr.raum_ID=r.raum_ID
LEFT OUTER JOIN MA_Telefon t ON (mf.persknr=t.persknr AND t.sort=1)
WHERE (fkt_bis IS NULL OR fkt_bis>=DATE(NOW())) AND (fkt_von IS NULL OR fkt_von<=DATE(NOW())) ";
$lim = " LIMIT $start,$zeil";
if (!$rag["bind"]) $bind = " AND "; else $bind = $rag["bind"];
if ($rag["fkt"]> -1)
{
if ($rag['nfkt'])
$where = " mf.fkt_ID <> '$rag[fkt]' ";
else
$where = " mf.fkt_ID = '$rag[fkt]' ";
}
if ($rag["fktg"]> -1)
if ($where) $where .= "$bind fktgr_ID = '$rag[fktg]' "; else $where = " fktgr_ID = '$rag[fktg]' ";
if ($rag["fktb"]> -1)
if ($where) $where .= "$bind fkt_bereich = '$rag[fktb]' "; else $where = " fkt_bereich = '$rag[fktb]' ";
if ($bl == 'b')
{
$where = " fkt_listb ='b'";
$rag["fkt"] = -1;
}
if ($where) $where = " AND ($where)";
$sql .= $where . ' ORDER BY 6,7,1,2' . $lim;
$result = $dbc ->queryObjectArray($sql);
if ($rag["fktg"] > -1)
{
$sql = "SELECT fktgr_bez FROM Funktion_Gruppe WHERE fktgr_ID = '" . $rag["fktg"] ."'";
$fktgbez = $dbc -> querySingleItem($sql);
$krit = " [Gruppierung = '$fktgbez']";
}
if ($rag["fkt"] > -1)
{
$sql = "SELECT fkt_bez FROM Funktion WHERE fkt_ID = '" . $rag["fkt"] ."'";
$fktbez = $dbc -> querySingleItem($sql);
if ($krit) $bd = " ".$bind; else $bd = "";
if ($rag["nfkt"]) $vgl = "<>"; else $vgl = "=";
$krit .= $bd." [Funktion $vgl '$fktbez']";
}
if ($rag["fktb"] > -1)
{
if ($krit) $bd = " ".$bind; else $bd = "";
$krit .= $bd." [Geltungsbereich = '" . $rag["fktb"] ."']";
}
if ($krit )echo "$krit"; else echo "";
if ($result)
{
$bg1 = "#F8F8F8";
$bg2 = "#DEDFE1";
$bg = "#FFFFFF";
$sqlc = "SELECT COUNT(*)
FROM fhiiqm.MA_Funktion mf INNER JOIN Mitarbeiter m ON mf.persknr=m.Persknr
INNER JOIN Funktion f ON mf.fkt_ID=f.fkt_ID
LEFT OUTER JOIN Abteilung a ON m.abt_tel=a.abt_ID
LEFT OUTER JOIN Mitarbeiter_Raum mr ON m.persknr=mr.persknr
LEFT OUTER JOIN Raum r ON mr.raum_ID=r.raum_ID
WHERE (fkt_bis IS NULL OR fkt_bis>=DATE(NOW())) AND (fkt_von IS NULL OR fkt_von<=DATE(NOW()))
$where";
$ianz = $dbc -> querySingleItem($sqlc);
//$ianz = $dbc -> numrows($sql); //liefert nur $zeil!
printf ("<p>Anzahl: %s</p>", $ianz);
// if ($bl) $class = " class='bigger'"; else $class=""; // nur fuer Beauftragte Institut
echo "<table border='0' cellspacing='0' cellpadding='2'>\n";
// Listenkopf
echo "<tr bgcolor='#68ACBF'>";
if ($rag["fkt"] == -1 || $rag["nfkt"]) echo "<th$class>Funktion</th>";
echo "<th$class>Geltungsbereich</th>";
echo "<th$class>Name</th>";
echo "<th$class>Abteilung</th>";
echo "<th$class>Gebäude</th>";
echo "<th$class>Raum-Nr.</th>";
echo "<th$class>Tel.</th>";
echo "<th$class>E-Mail</th></tr>\n";
foreach ($result as $row)
{
$sqla = "SELECT DISTINCT CASE WHEN abt_tel<> 7 THEN a.abt_name ELSE
(CASE WHEN rag.abt_ID = 7 THEN rag.rag_bezk ELSE '' END) END AS abt_name
FROM MA_Funktion mf
LEFT OUTER JOIN fhiiqm.Mitarbeiter m ON mf.persknr=m.persknr
LEFT OUTER JOIN fhiiqm.Abteilung a ON m.abt_tel=a.abt_ID
LEFT OUTER JOIN fhiiqm.MA_RaumArbGruppe ma ON mf.persknr=ma.persknr
LEFT OUTER JOIN fhiiqm.RaumArb_Gruppe rag ON ma.rag_ID=rag.rag_ID
WHERE mf_ID = ".$row->mf_ID .
" ORDER BY rag.abt_ID DESC";
$abtn = $dbc -> querySingleItem($sqla);
//echo "sql = $sqla<br />, $abtn<br />";
if ($bg == $bg1) $bg = $bg2; else $bg = $bg1;
echo "<tr bgcolor='" . $bg . "'>";
if ($rag["fkt"] == -1 || $rag["nfkt"]) echo "<td$class>$row->fkt_bez $row->fkt_bem</td>";
echo "<td$class>$row->fkt_bereich</td>";
echo "<td$class>$row->titel $row->nachname, $row->vorname</td>";
echo "<td$class>$abtn</td>";
echo "<td$class>$row->geb_ID</td>";
echo "<td$class>$row->raum_nr</td>";
echo "<td$class>$row->tel</td>";
echo "<td$class><a href='mailto:$row->email'>$row->email</a></td></tr>\n";
}
echo "</table>\n";
echo "<p></p><table width=60%><tr><td class='right'><a href=\"javascript:window.print()\" class='sc'>
<img src=\"/fhiiqm/img/printer.png\" alt=\"Bericht drucken\" border=\"0\" align=\"right\" title=\"Bericht drucken\"></a></td></tr></table>";
echo "</div>\n";
echo "<br /> ";
}
else
{
echo "<p class='red'> Leider gibt es kein Ergebnis für die von Ihnen gewählten Kriterien!</p>";
echo "</div>\n";
}
if (!isset($_GET["s"]))
echo "<br /><br /> <a href=\"" . $_SERVER["PHP_SELF"] . "\" target=\"_self\" title=\"Gefahren\" class=\"sc\">neuer Bericht Beauftragte</a></p>\n";
}
?>
</body>
</html>