glpi-plugin-auf/plugin/report.operatingsystems.php

   1 <?php
   2 //Options for GLPI 0.71 and newer : need slave db to access the report
   3 $USEDBREPLICATE=1;
   4 $DBCONNECTION_REQUIRED=0;
   5
   6 include ("../../inc/includes.php");
   7
   8 Session::checkRight("reports", "r");
   9 Session::checkRight("computer", "r");
  10
  11 $report_title = "Systèmes d'exploitation par implantation";
  12
  13 #Html::header(Report::getTypeName(2), $_SERVER['PHP_SELF'], "utils", "report");
  14 Html::header($report_title, $_SERVER['PHP_SELF'], "utils", "report");
  15 Report::title();
  16
  17 echo "\n<p class='big b' style='margin: 5px; text-align: center'><span id='report_date' style='float: right; margin: 2px 20px; font-size: 75%; font-style: italic'>(".date('Y-m-d H:i:s')." UTC)</span>$report_title</p>";
  18
  19 $computer = new Computer();
  20
  21 $itemtype = 'Computer';
  22 $table_item = getTableForItemType($itemtype);
  23
  24 //echo "<span class='big b'>Systèmes d'exploitation par implantation</span><br><br>";
  25 echo "<table class='tab_cadrehov'>";
  26 echo "<tr class='tab_bg_1'><th>Implantation</th><th>Système d'exploitation</th><th>Quantité</th></tr>";
  27 $green_grand_total = 0;
  28 $yellow_grand_total = 0;
  29 $red_grand_total = 0;
  30 $blue_grand_total = 0;
  31 $grand_total = 0;
  32
  33 # parcours des régions
  34 $query = "SELECT id,name,completename FROM glpi_entities WHERE (id=0 OR entities_id=0)";
  35 $query .= " ".getEntitiesRestrictRequest("AND", "glpi_entities");
  36 $region_result = $DB->query($query);
  37 while ($region_data = $DB->fetch_assoc($region_result)) {
  38   $region_id = $region_data['id'];
  39   if ($region_id != 0) {
  40     $region_name = getTreeLeafValueName("glpi_entities", $region_id);
  41     echo "<tr><th colspan='3'></th></tr>\n";
  42   } else {
  43     $region_name = "AUF (non classé)";
  44   }
  45   echo "<tr class='tab_bg_1'><td colspan='3' class='b'>$region_name</td></tr>";
  46
  47   $green_total = 0;
  48   $yellow_total = 0;
  49   $red_total = 0;
  50   $blue_total = 0;
  51   $region_total = 0;
  52
  53 # operating systems per computer
  54 $query = "SELECT COUNT(c.id) AS count, e.name AS ename,
  55           (CASE WHEN o.name REGEXP '^Debian ' THEN SUBSTRING_INDEX(REPLACE(o.name,' GNU/Linux',''),'.',1)
  56                 WHEN o.name REGEXP '^Ubuntu ' THEN SUBSTRING_INDEX(REPLACE(o.name,' LTS',''),'.',2)
  57                 WHEN o.name REGEXP '^Microsoft' THEN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(o.name,'™',''),'®',''),'fessional',''),'fessionnel',''),'Microsoft ','')
  58                 WHEN o.name REGEXP '^Mac ' THEN REPLACE(o.name,'Mac ','')
  59                 ELSE o.name END) AS oname
  60           FROM `$table_item` c
  61           LEFT JOIN glpi_entities e ON (c.entities_id = e.id)
  62           LEFT JOIN glpi_operatingsystems o ON (c.operatingsystems_id = o.id)
  63           WHERE c.is_deleted = '0' AND c.is_template = '0'
  64           ".getEntitiesRestrictRequest("AND", "c");
  65 if ($region_id != 0) {
  66   $query .= " AND ".getRealQueryForTreeItem("glpi_entities", $region_id, "e.id");
  67 } else {
  68   $query .= " AND e.id = '0'";
  69 }
  70 $query .= " GROUP BY 2,3 ORDER BY 2,3";
  71 $result = $DB->query($query);
  72 while ($data=$DB->fetch_assoc($result)) {
  73   if (empty($data['ename'])) {
  74     $data['ename'] = "<i>(indéterminé)</i>";
  75   }
  76   if (empty($data['oname'])) {
  77     $data['oname'] = "<i>(indéterminé)</i>";
  78   }
  79   if (($data['oname'] == 'Debian 7') || ($data['oname'] == 'Ubuntu 12.04')) {
  80     $oname_plus = '<svg width="5" height="5" style="background-color: #00ff00; margin: 1px;"/>';
  81     $green_total += $data['count'];
  82   } elseif (($data['oname'] == 'Debian 6') || ($data['oname'] == 'Ubuntu 10.04')) {
  83     $oname_plus = '<svg width="5" height="5" style="background-color: #ffff00; margin: 1px;"/>';
  84     $yellow_total += $data['count'];
  85   } elseif ((substr($data['oname'],0,7) == 'Windows') || (substr($data['oname'],strlen($data['oname'])-4,4) == 'OS X')) {
  86     $oname_plus = '<svg width="5" height="5" style="background-color: #0000ff; margin: 1px;"/>';
  87     $blue_total += $data['count'];
  88   } else {
  89     $oname_plus = '<svg width="5" height="5" style="background-color: #ff0000; margin: 1px;"/>';
  90     $red_total += $data['count'];
  91   }
  92   echo "<tr class='tab_bg_2 auf_show_details'>";
  93   echo "<td>&nbsp;&nbsp;".$data['ename']."</td>";
  94   echo "<td>".$oname_plus."&nbsp;".$data['oname']."</td>";
  95   echo "<td class='numeric'>".$data['count']."</td></tr>";
  96   $region_total += $data['count'];
  97 }
  98
  99 $green_svg = '<svg width="'.(400*$green_total/$region_total).'" height="5" style="background-color: #00ff00; margin: 1px 0;"/>';
 100 $yellow_svg = '<svg width="'.(400*$yellow_total/$region_total).'" height="5" style="background-color: #ffff00; margin: 1px 0;"/>';
 101 $red_svg = '<svg width="'.(400*$red_total/$region_total).'" height="5" style="background-color: #ff0000; margin: 1px 0;"/>';
 102 $blue_svg = '<svg width="'.(400*$blue_total/$region_total).'" height="5" style="background-color: #0000ff; margin: 1px 0;"/>';
 103 echo "<tr class='tab_bg_2'><td class='b'>&nbsp;&nbsp;Sous-total</td>";
 104 echo "<td>".$green_svg.$yellow_svg.$red_svg.$blue_svg."</td>";
 105 echo "<td class='numeric b'>$region_total</td></tr>";
 106
 107 $green_grand_total += $green_total;
 108 $yellow_grand_total += $yellow_total;
 109 $red_grand_total += $red_total;
 110 $blue_grand_total += $blue_total;
 111 $grand_total += $region_total;
 112 } // fin des régions
 113
 114 #echo "<tr class='tab_bg_1'><td colspan='3'>&nbsp;</td></tr>";
 115 echo "<tr><th colspan='3'></th></tr>\n";
 116
 117 # total operating systems
 118 $query = "SELECT COUNT(*) FROM `".$table_item."`
 119           LEFT JOIN `glpi_computers_items`
 120           ON (`glpi_computers_items`.`itemtype` = '".$itemtype."'
 121           AND `glpi_computers_items`.`items_id` = `".$table_item."`.`id`)
 122           WHERE `".$table_item."`.`is_deleted` = '0'
 123             AND `".$table_item."`.`is_template` = '0' ".
 124           getEntitiesRestrictRequest("AND", $table_item);
 125 $result = $DB->query($query);
 126 $total = $DB->result($result, 0, 0);
 127
 128 $green_svg = '<svg width="'.(400*$green_grand_total/$grand_total).'" height="5" style="background-color: #00ff00; margin: 1px 0;"/>';
 129 $yellow_svg = '<svg width="'.(400*$yellow_grand_total/$grand_total).'" height="5" style="background-color: #ffff00; margin: 1px 0;"/>';
 130 $red_svg = '<svg width="'.(400*$red_grand_total/$grand_total).'" height="5" style="background-color: #ff0000; margin: 1px 0;"/>';
 131 $blue_svg = '<svg width="'.(400*$blue_grand_total/$grand_total).'" height="5" style="background-color: #0000ff; margin: 1px 0;"/>';
 132 echo "<tr class='tab_bg_1'><td class='b'>Total général</td>";
 133 echo "<td>".$green_svg.$yellow_svg.$red_svg.$blue_svg."</td>";
 134 echo "<td class='numeric b'>$total</td></tr>";
 135
 136 echo "</table>";
 137
 138 Html::footer();
 139 ?>