--- a/fhiiqm/keytyp_tract_ma.php Thu Sep 12 09:04:56 2013 +0200
+++ b/fhiiqm/keytyp_tract_ma.php Fri Dec 20 15:19:32 2013 +0100
@@ -1,269 +1,270 @@
-<?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="55%" 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 0 THEN 'inaktiv' ELSE 'aktiv' END, ')') AS nvname
- FROM fhiiqm.Mitarbeiter m INNER JOIN Abteilung a ON m.abt_tel=a.abt_ID
- $wher AND 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>
+<?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="55%" 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>
\ No newline at end of file