<?php
/**
* @author Bettina Schwarzer, Fritz-Haber-Institut
* @copyright 06/2013
*
* Keytyp Transaktion Depot - MA
*/
error_reporting(E_ALL ^ E_NOTICE);
session_start();
if (! isset($_SESSION["userid"]))
{
include_once ("inc/func_lib.inc.php");
login($_SERVER["PHP_SELF"]);
exit;
}
if (is_null($_SESSION["recht"]) || (!is_null($_SESSION["recht"]) && !in_array("kr",$_SESSION["recht"]) && !in_array("ke",$_SESSION["recht"])))
{
header("Location: start.php");
exit;
}
include_once($_SERVER['DOCUMENT_ROOT'] ."/fhiiqm/inc/dbconnect.inc.php");
if (!isset($dbc) || !$dbc) $dbc = new dbconnection();
// zulaessige Depots
$uid = $_SESSION["userid"];
$sql = "SELECT depot_ID FROM KeyDepot_userweb WHERE userid = '$uid'";
$res = $dbc -> queryObjectArray($sql);
foreach ($res as $row)
$adep[] = $row->depot_ID;
// print_r($adep); echo "<br />";
$aaction = array("get" => "Rücknahme", "out" => "Ausgabe", "lost" => "Verlust"); //Bezeichnung Transfer
?>
<!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>Keytyp MA transferieren</title>
</head>
<body>
<?php
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=\"img/key.png\" border=\"0\" alt=\"Keytyp transferieren\" title=\"Depotverwaltung Schlüsseltyp\"/></div>\n";
echo "<div align=\"center\">";
echo "<h3>Schlüssel-Transfer Depot <-> Mitarbeiter</h3>";
echo "</div>\n";
if (!isset($adep))
echo "<p class='red'> Sie haben nicht das Recht, Transfers von Schlüsseln zu Mitarbeitern vorzunehmen. Bitte wenden Sie sich an Frau Kohl.</p>";
else
{
$out = $_POST["out"];
$get = $_POST["get"];
$lost = $_POST["lost"];
$ky = $_POST["ky"];
if (!isset($ky["anz"])) $ky["anz"]=1;
//print_r($ky); echo "<br />";
if (isset($ky) && $get)
{
$sql = "SELECT IFNULL(SUM(CASE WHEN pers_action='get' THEN IFNULL(pers_anz,0) ELSE 0 END),0) - IFNULL(SUM(CASE WHEN pers_action='out' THEN IFNULL(pers_anz,0) ELSE 0 END),0) - IFNULL(SUM(CASE WHEN pers_action='lost' THEN IFNULL(pers_anz,0) ELSE 0 END),0) AS bestand
FROM fhiiqm.Keytyp_MA WHERE persknr='".$ky["ma"]."' AND keytyp_ID = '". $ky["kt"] ."'";
$anzma = $dbc->querySingleItem($sql);
if (is_numeric($ky["anz"]) && $anzma < $ky["anz"]) $err = "Rücknahme: Bestand '". $ky["kt"] ."' Mitarbeiter '". $ky["ma"] . "': <b>$anzma < ".$ky["anz"]."</b> (Transfer-Anzahl)!";
}
if (isset($ky) && $lost)
{
$sql = "SELECT IFNULL(SUM(CASE WHEN pers_action='get' THEN IFNULL(pers_anz,0) ELSE 0 END),0) - IFNULL(SUM(CASE WHEN pers_action='out' THEN IFNULL(pers_anz,0) ELSE 0 END),0) - IFNULL(SUM(CASE WHEN pers_action='lost' THEN IFNULL(pers_anz,0) ELSE 0 END),0) AS bestand
FROM fhiiqm.Keytyp_MA WHERE persknr='".$ky["ma"]."' AND keytyp_ID = '". $ky["kt"] ."'";
$anzma = $dbc->querySingleItem($sql);
if (is_numeric($ky["anz"]) && $anzma < $ky["anz"]) $err = "Verlust: Bestand '". $ky["kt"] ."' Mitarbeiter '". $ky["ma"] . "': <b>$anzma < ".$ky["anz"]."</b> (Transfer-Anzahl)!";
}
if (isset($ky) && $out)
{
$sql = "SELECT IFNULL(SUM(CASE WHEN depot_action='get' THEN IFNULL(depot_anz,0) ELSE 0 END),0) - IFNULL(SUM(CASE WHEN depot_action='out' THEN IFNULL(depot_anz,0) ELSE 0 END),0) - IFNULL(SUM(CASE WHEN depot_action='lost' THEN IFNULL(depot_anz,0) ELSE 0 END),0) AS bestand
FROM fhiiqm.Keytyp_Depot WHERE depot_ID='".$ky["dep"]."' AND keytyp_ID = '". $ky["kt"] ."'";
$anzdep = $dbc->querySingleItem($sql);
if (is_numeric($ky["anz"]) && $anzdep < $ky["anz"]) $err = "Ausgabe: Bestand '". $ky["kt"] ."' Depot '".$ky["dep"]."': <b>$anzdep < ".$ky["anz"]."</b> (Transfer-Anzahl)!";
}
if (isset($ky["anz"]) && !is_numeric($ky["anz"]) && ($out || $get || $lost)) $err = "Anzahl ist ncht numerisch!";
if (!isset($ky) || (!$out && !$get && !$lost) || $err)
{
/* form: 'accept-charset="utf-8"' wegen Umlauten in Formfeldern und Nichtnutzen mysqli, sondern sp für INSERTS */
?>
<div align="center">
<h5>* - Felder sind erforderlich!</h5>
</div>
<form action="<?php $_SERVER['PHP_SELF'] ?>" method="post" enctype="application/x-www-form-urlencoded" name="form_key" accept-charset="utf-8">
<div align="center">
<?php
if ($err) echo "<p class='red'>$err</p>\n";
?>
<table width="60%" border="0" cellspacing="3" cellpadding="3">
<tr>
<td>Depot *</td><td>Schlüsseltyp *</td><td>Anzahl *</td><td>Mitarbeiter *</td>
</tr>
<tr>
<td><select name="ky[dep]" onchange="this.form.submit();">
<?php
$strdep = "'". implode("','",$adep) ."'";
$sql = "SELECT * FROM fhiiqm.KeyDepot WHERE depot_ID IN ($strdep)";
if ($res = $dbc -> queryObjectArray($sql))
foreach ($res as $row)
{
if (!$ky['dep'] && !$beg) $ky['dep'] = $row->depot_ID;
if ($row->depot_ID == $ky['dep']) $select = "selected='selected'"; else $select = "";
echo "<option $select value=\"$row->depot_ID\">$row->depot_ID: $row->depot_bez</option>\n";
$beg=1;
}
$beg=0;
?>
</select>
</td>
<td><select name="ky[kt]" onchange="this.form.submit();">
<?php
$sql = "SELECT key_level,d.keytyp_ID,keytyp_bez,
SUM(CASE WHEN depot_action='get' THEN IFNULL(depot_anz,0) ELSE 0 END) - SUM(CASE WHEN depot_action='out' THEN IFNULL(depot_anz,0) ELSE 0 END) - SUM(CASE WHEN depot_action='lost' THEN IFNULL(depot_anz,0) ELSE 0 END) AS bestand
FROM fhiiqm.Keytyp_Depot d INNER JOIN Keytyp k ON d.keytyp_ID=k.keytyp_ID
WHERE depot_ID = '" . $ky['dep'] . "'
GROUP BY key_level,d.keytyp_ID,keytyp_bez";
if ($res = $dbc -> queryObjectArray($sql))
foreach ($res as $row)
{
if (!$ky['kt'] && !$beg)
$ky['kt'] = $row->keytyp_ID;
if ($row->keytyp_ID == $ky['kt']) $select = "selected='selected'"; else $select = "";
echo "<option $select value=\"$row->keytyp_ID\">$row->key_level - $row->keytyp_ID [$row->keytyp_bez] ($row->bestand)</option>\n";
$beg=1;
}
$beg=0;
?>
</select>
</td>
<td><input class="right" name="ky[anz]" type="text" size="5" maxlength="3" value="<?php echo $ky['anz']; ?>" /></td>
<td><select name="ky[ma]" onchange="this.form.submit();">
<?php
if ($ky['dep'] == 'FHI') $wher = " WHERE 1=1";
else $wher = "INNER JOIN KeyDepot_Abteilung k ON a.abt_ID=k.abt_ID WHERE depot_ID = '" . $ky['dep'] . "'";
$sql = "SELECT persknr,CONCAT(TRIM(nachname), ', ',IFNULL(vorname,''),' (',
CASE aktiv WHEN 1 THEN 'aktiv' ELSE (CASE aktiv WHEN -1 THEN 'Gast' ELSE 'inaktiv' END) END, ')') AS nvname
FROM fhiiqm.Mitarbeiter m INNER JOIN Abteilung a ON m.abt_tel=a.abt_ID
$wher AND ABS(m.aktiv)<2
ORDER BY nvname";
// echo "$sql<br />";
if ($res = $dbc -> queryObjectArray($sql))
{
foreach ($res as $row)
$apers[] = $row->persknr;
if (!in_array($ky['ma'],$apers)) $ky['ma'] = null;
foreach ($res as $row)
{
if (!$ky['ma'] && !$beg) $ky['ma'] = $row->persknr;
if ($row->persknr == $ky['ma']) $select = "selected='selected'"; else $select = "";
echo "<option $select value=\"$row->persknr\">$row->nvname</option>\n";
$beg=1;
}
$beg=0;
}
?>
</select>
<?php
$sql = "SELECT IFNULL(IFNULL(SUM(CASE WHEN pers_action='get' THEN IFNULL(pers_anz,0) ELSE 0 END),0)
- IFNULL(SUM(CASE WHEN pers_action='out' THEN IFNULL(pers_anz,0) ELSE 0 END),0)
- IFNULL(SUM(CASE WHEN pers_action='lost' THEN IFNULL(pers_anz,0) ELSE 0 END),0),0) AS bestand
FROM Keytyp_MA WHERE persknr=".$ky['ma']." AND keytyp_ID='".$ky['kt']."'";
// echo "sql anzahl keytyp_ID = $sql<br />";
$anzkey = $dbc -> querySingleItem($sql);
echo " ($anzkey)";
?>
</td>
</tr>
<tr><td>Bemerkung</td><td colspan="2"><textarea name="ky[bem]" cols="50" rows="2"><?php echo $ky['bem']; ?></textarea></td>
<?php
// Schluessel / MA anzeigen
$sql = "SELECT key_level,m.keytyp_ID,
SUM(CASE WHEN pers_action='get' THEN IFNULL(pers_anz,0) ELSE 0 END) -
SUM(CASE WHEN pers_action='out' THEN IFNULL(pers_anz,0) ELSE 0 END) -
SUM(CASE WHEN pers_action='lost' THEN IFNULL(pers_anz,0) ELSE 0 END) AS bestand
FROM fhiiqm.Keytyp_MA m INNER JOIN Keytyp k ON m.keytyp_ID=k.keytyp_ID
WHERE persknr=".$ky['ma']."
GROUP BY key_level,m.keytyp_ID
ORDER BY key_level,m.keytyp_ID";
if ($res = $dbc -> queryObjectArray($sql))
{
echo "<td>";
// echo "sql alle keytyp_ma = $sql<br />";
foreach ($res as $row)
{
if (!$beg) $br = ""; else $br = "<br />";
echo "$br$row->key_level - $row->keytyp_ID: $row->bestand";
$beg=1;
}
echo "</td>";
}
echo "</tr>";
if (in_array("ke",$_SESSION["recht"]))
{
?>
<tr><td></td><td><input class="button" type="submit" name="out" value="-> Ausgabe " title="Ausgabe"/></td></tr>
<tr><td></td><td><input class="button" type="submit" name="get" value="<- Rücknahme" title="Rueckgabe"/></td></tr>
<tr><td></td><td><input class="button" type="submit" name="lost" value="<- Verlust " title="Verlust"/></td></tr>
<?php
}
?>
</table>
<?php
}
else
{
echo "<div align=\"center\">";
// print_r($ky); echo "$get,$out,$lost<br />";
$umls = array("Ä","Ö","Ü","ä","ö","ü","ß");
$umlr = array("Ae","Oe","Ue","ae","oe","ue","ss");
if ($get) $action = "get";
elseif ($out) $action = "out";
elseif ($lost) $action = "lost";
if (isset($ky) && $ky)
{
// $ky["bem"] = str_replace($umls,$umlr,str_replace("'","\"",substr($ky["bem"],0,100)));
$ky["bem"] = str_replace("'","\"",substr($ky["bem"],0,100));
$sql = "SELECT CONCAT(nachname,', ',IFNULL(vorname,'')) AS nvname FROM Mitarbeiter WHERE persknr=".$ky["ma"];
$nvname = $dbc->querySingleItem($sql);
if (is_numeric($ky["anz"]) && $ky["anz"] != 0)
{
$sql = "CALL keytyp_tract_ma('".$ky["dep"]."','".$ky["ma"]."','".$ky["kt"]."',".$ky["anz"].",'$action','" .$ky["bem"]."',@suc)";
$result = $dbc ->queryObjectArray($sql);
// $suc = 1;
// if ($suc)
if ($dbc -> querySingleItem("SELECT @suc"))
echo "<p class='green'><b>" . $aaction["$action"].": ".$ky["anz"]. " Schlüssel '". $ky['kt'] .
"' wurde(n) erfolgreich zwischen Depot '".$ky["dep"]. "' und '$nvname' transferiert.</b></p>\n";
else
echo "<p class='red'><b>" . $aaction["$action"].": ".$ky["anz"]. " Schlüssel '". $ky['kt'] .
"' konnte(n) nicht zwischen Depot '".$ky["dep"]. "' und '$nvname' transferiert werden.</b></p>\n";
}
else
echo "<p class='red'><b>" . $aaction["$action"].": '".$ky["anz"]. "' Schlüssel '". $ky['kt'] .
"' konnte(n) nicht zwischen Depot '".$ky["dep"]. "' und '$nvname' transferiert werden.</b></p>\n";
$sql = "SELECT SUM(CASE WHEN depot_action='get' THEN IFNULL(depot_anz,0) ELSE 0 END) - SUM(CASE WHEN depot_action='out' THEN IFNULL(depot_anz,0) ELSE 0 END) - SUM(CASE WHEN depot_action='lost' THEN IFNULL(depot_anz,0) ELSE 0 END) AS bestand
FROM fhiiqm.Keytyp_Depot WHERE depot_ID='".$ky["dep"]."' AND keytyp_ID = '". $ky["kt"] ."'";
$anzdep = $dbc->querySingleItem($sql);
echo "<p>Aktueller Bestand Schlüsseltyp '". $ky["kt"] ."' im Depot ". $ky["dep"] . ": $anzdep</p><br /><br />\n";
}
echo "</div>\n";
echo "<p class='sc'> <a href=\"".$_SERVER["PHP_SELF"]."\" target=\"_self\" title=\"Transfer Depot-MA\">Weiterer Transfer Depot-MA</a></p>\n";
}
}
?>
</body>
</html>