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