Mercurial Mercurial > IQM / comparison
summary | shortlog | changelog | graph | tags | bookmarks | branches | files | changeset | file | latest | revisions | annotate | diff | comparison | raw | help
Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
fhiiqm/report/keytyp_bestand_all_report.php
changeset 41 9c668cd391fa
parent 40 6f4b105daa24
child 42 cfc409017ba8
equal deleted inserted replaced
40:6f4b105daa24 41:9c668cd391fa 
   137                 </select>
 
   137                 </select>
 
   138             </tr>
 
   138             </tr>
 
   139             <tr>
 
   139             <tr>
 
   140                 <td>Sortierung nach<br />(in dieser Reihenfolge)</td><td>
 
   140                 <td>Sortierung nach<br />(in dieser Reihenfolge)</td><td>
 
   141             <?php
 
   141             <?php
 
   142                 $sort = array("-1"=>"","1"=>"Schl&uuml;sseltyp","2"=>"Level","3"=>"Besitzer");
 
   142                 $sort = array("-1"=>"","1"=>"Schl&uuml;sseltyp","2"=>"Level","3"=>"Besitzer","4"=>"Abteilung");
 
   143                 for ($i=1; $i<3; $i++)
 
   143                 for ($i=1; $i<4; $i++)
 
   144                 { 
   144                 { 
   145                   echo "$i.&nbsp;&nbsp;<select name='ky[sort$i]'>\n";
 
   145                   echo "$i.&nbsp;&nbsp;<select name='ky[sort$i]'>\n";
 
   146                   foreach ($sort as $key=>$val)
 
   146                   foreach ($sort as $key=>$val)
 
   147                   {
 
   147                   {
 
   148                     $rsort = "sort" . $i;
 
   148                     $rsort = "sort" . $i;
 
   195                 $akeyt[] = $row->keytyp_ID;
 
   195                 $akeyt[] = $row->keytyp_ID;
 
   196             $strkt = "'". implode("','",$akeyt) ."'";
 
   196             $strkt = "'". implode("','",$akeyt) ."'";
 
   197             $wherk = " AND h.keytyp_ID IN ($strkt) ";
 
   197             $wherk = " AND h.keytyp_ID IN ($strkt) ";
 
   198             $wherd = " AND h.depot_ID = '".$ky["dep"]."'";
 
   198             $wherd = " AND h.depot_ID = '".$ky["dep"]."'";
 
   199         }
 
   199         }
 
   200         if ($ky["sort1"]>0 || $ky["sort2"]>0)
 
   200         if ($ky["sort1"]>0 || $ky["sort2"]>0 || $ky["sort3"]>0)
 
   201         {
 
   201         {
 
   202             if ($ky["sort1"]>0) $ord  = " ORDER BY " . $ky["sort1"];
 
   202             if ($ky["sort1"]>0) $ord  = " ORDER BY " . $ky["sort1"];
 
   203             if ($ky["sort2"]>0) $ord .= ",".$ky["sort2"];
 
   203             if ($ky["sort2"]>0) $ord .= ",".$ky["sort2"];
 
       
   204             if ($ky["sort3"]>0) $ord .= ",".$ky["sort3"];
 
   204         }
 
   205         }
 
   205          
   206          
   206         $sql = "SELECT h.keytyp_ID, key_level, 
   207         $sql = "SELECT h.keytyp_ID, key_level, 
   207                     CONCAT (nachname, ', ',vorname, ', ' , IFNULL(abt_name,''),' (',CASE aktiv WHEN 0 THEN 'inaktiv' ELSE 'aktiv' END, ')') AS keyowner, h.depot_ID, 'ma' AS tab,  
   208                     CONCAT (nachname, ', ',vorname, ', ' , IFNULL(abt_name,''),' (',CASE aktiv WHEN 0 THEN 'inaktiv' ELSE 'aktiv' END, ')') AS keyowner, IFNULL(abt_name,'') AS abt, h.depot_ID, 'ma' AS tab,  
   208                 	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
 
   209                 	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
 
   209                 	FROM fhiiqm.Mitarbeiter m 
   210                 	FROM fhiiqm.Mitarbeiter m 
   210                 	INNER JOIN Keytyp_MA h ON m.persknr=h.persknr
 
   211                 	INNER JOIN Keytyp_MA h ON m.persknr=h.persknr
 
   211                     INNER JOIN Keytyp k ON h.keytyp_ID = k.keytyp_ID 
   212                     INNER JOIN Keytyp k ON h.keytyp_ID = k.keytyp_ID 
   212                     LEFT OUTER JOIN Abteilung a ON m.abt_tel=a.abt_ID
 
   213                     LEFT OUTER JOIN Abteilung a ON m.abt_tel=a.abt_ID
 
   213                 	WHERE m.aktiv<2
 
   214                 	WHERE m.aktiv<2
 
   214 					GROUP BY h.keytyp_ID, key_level,keyowner
 
   215 					GROUP BY h.keytyp_ID, key_level,keyowner,abt
 
   215                     HAVING bestand > 0 $where $wherk
 
   216                     HAVING bestand > 0 $where $wherk
 
   216                 UNION
 
   217                 UNION
 
   217                 SELECT h.keytyp_ID, key_level,CONCAT(depot_ID,'-Depot') AS keyowner, h.depot_ID, 'dep' AS tab, 
   218                 SELECT h.keytyp_ID, key_level,CONCAT(depot_ID,'-Depot') AS keyowner, depot_ID AS abt, h.depot_ID, 'dep' AS tab, 
   218                     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
 
   219                     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
 
   219                     FROM fhiiqm.Keytyp_Depot h INNER JOIN Keytyp k ON h.keytyp_ID = k.keytyp_ID
 
   220                     FROM fhiiqm.Keytyp_Depot h INNER JOIN Keytyp k ON h.keytyp_ID = k.keytyp_ID
 
   220                     GROUP BY h.keytyp_ID, key_level,keyowner
 
   221                     GROUP BY h.keytyp_ID, key_level,keyowner,abt
 
   221                     HAVING bestand > 0 $where $wherd";
 
   222                     HAVING bestand > 0 $where $wherd";
 
   222         $ianz = $dbc -> numrows($sql); 
   223         $ianz = $dbc -> numrows($sql); 
   223         $sql .= $ord . $lim;
 
   224         $sql .= $ord . $lim;
 
   224 //echo $sql . "<br />";
 
   225 //echo $sql . "<br />";
 
   225         $result = $dbc ->queryObjectArray($sql);
 
   226         $result = $dbc ->queryObjectArray($sql);
 
   252             echo "<tr bgcolor='#68ACBF'>";
 
   253             echo "<tr bgcolor='#68ACBF'>";
 
   253             if ($ky["sort1"] == 2)
 
   254             if ($ky["sort1"] == 2)
 
   254                 echo "<th>Level</th><th>Schl&uuml;sseltyp</th>";
 
   255                 echo "<th>Level</th><th>Schl&uuml;sseltyp</th>";
 
   255             else            
   256             else            
   256                 echo "<th>Schl&uuml;sseltyp</th><th>Level</th>";
 
   257                 echo "<th>Schl&uuml;sseltyp</th><th>Level</th>";
 
   257             echo "<th>Besitzer</th><th>Anzahl</th>";
 
   258             echo "<th>Besitzer</th><th>Abt.</th><th>Anzahl</th>";
 
   258             $anzt = 0;
 
   259             $anzt = 0;
 
   259             foreach ($result as $row)
 
   260             foreach ($result as $row)
 
   260             {
 
   261             {
 
   261         		if ($bg == $bg1) $bg = $bg2; else $bg = $bg1;
 
   262         		if ($bg == $bg1) $bg = $bg2; else $bg = $bg1;
 
   262                 echo "<tr bgcolor='" . $bg . "'>";
 
   263                 echo "<tr bgcolor='" . $bg . "'>";
 
   264                     echo "<td>$row->key_level</td><td>$row->keytyp_ID</td>";
 
   265                     echo "<td>$row->key_level</td><td>$row->keytyp_ID</td>";
 
   265                 else
 
   266                 else
 
   266                     echo "<td>$row->keytyp_ID</td><td>$row->key_level</td>";
 
   267                     echo "<td>$row->keytyp_ID</td><td>$row->key_level</td>";
 
   267                 if ($row->tab == 'dep') $cl = " class = 'bold'"; else $cl = "";
 
   268                 if ($row->tab == 'dep') $cl = " class = 'bold'"; else $cl = "";
 
   268                 echo "<td$cl>$row->keyowner</td>";
 
   269                 echo "<td$cl>$row->keyowner</td>";
 
       
   270                 echo "<td>$row->abt</td>";
 
   269                 echo "<td class='right'>$row->bestand</td>"; 
   271                 echo "<td class='right'>$row->bestand</td>"; 
   270                 echo "</tr>";
 
   272                 echo "</tr>";
 
   271                 $anzt += $row->bestand;
 
   273                 $anzt += $row->bestand;
 
   272             }
 
   274             }
 
   273             echo "<tr><td colspan='2'>&nbsp;</td><td>Summe</td><td class='right'>$anzt</td></tr>";
 
   275             echo "<tr><td colspan='2'>&nbsp;</td><td colspan='2'>Summe</td><td class='right'>$anzt</td></tr>";
 
   274             echo "</table>\n";
 
   276             echo "</table>\n";
 
   275             echo "<p></p><table width=40%><tr><td class='right'><a href=\"javascript:window.print()\" class='sc'>
 
   277             echo "<p></p><table width=40%><tr><td class='right'><a href=\"javascript:window.print()\" class='sc'>
 
   276                 <img src=\"/fhiiqm/img/printer.png\" alt=\"Bericht drucken\" border=\"0\" align=\"right\" title=\"Bericht drucken\"></a></td></tr></table>";
 
   278                 <img src=\"/fhiiqm/img/printer.png\" alt=\"Bericht drucken\" border=\"0\" align=\"right\" title=\"Bericht drucken\"></a></td></tr></table>";
 
   277             echo "</div>\n";
 
   279             echo "</div>\n";
 
   278         
   280
IQM