/** * @param $itemtype * @param $type * @param $begin (default '') * @param $end (default '') * @param $param (default '') * @param $value (default '') * @param $value2 (default '') */ static function constructEntryValues($itemtype, $type, $begin = "", $end = "", $param = "", $value = "", $value2 = "") { global $DB; if (!($item = getItemForItemtype($itemtype))) { return; } $table = $item->getTable(); $fkfield = $item->getForeignKeyField(); if (!($userlinkclass = getItemForItemtype($item->userlinkclass))) { return; } $userlinktable = $userlinkclass->getTable(); if (!($grouplinkclass = getItemForItemtype($item->grouplinkclass))) { return; } $grouplinktable = $grouplinkclass->getTable(); if (!($supplierlinkclass = getItemForItemtype($item->supplierlinkclass))) { return; } $supplierlinktable = $supplierlinkclass->getTable(); $tasktable = getTableForItemType($item->getType() . 'Task'); $closed_status = $item->getClosedStatusArray(); $solved_status = array_merge($closed_status, $item->getSolvedStatusArray()); $query = ""; $WHERE = "WHERE NOT `{$table}`.`is_deleted` " . getEntitiesRestrictRequest("AND", $table); $LEFTJOIN = ""; $LEFTJOINUSER = "******"; $LEFTJOINGROUP = "LEFT JOIN `{$grouplinktable}`\n ON (`{$grouplinktable}`.`{$fkfield}` = `{$table}`.`id`)"; $LEFTJOINSUPPLIER = "LEFT JOIN `{$supplierlinktable}`\n ON (`{$supplierlinktable}`.`{$fkfield}` = `{$table}`.`id`)"; switch ($param) { case "technicien": $LEFTJOIN = $LEFTJOINUSER; $WHERE .= " AND (`{$userlinktable}`.`users_id` = '{$value}'\n AND `{$userlinktable}`.`type`='" . CommonITILActor::ASSIGN . "')"; break; case "technicien_followup": $WHERE .= " AND `{$tasktable}`.`users_id` = '{$value}'"; $LEFTJOIN = " LEFT JOIN `{$tasktable}`\n ON (`{$tasktable}`.`{$fkfield}` = `{$table}`.`id`)"; break; case "user": $LEFTJOIN = $LEFTJOINUSER; $WHERE .= " AND (`{$userlinktable}`.`users_id` = '{$value}'\n AND `{$userlinktable}`.`type` ='" . CommonITILActor::REQUESTER . "')"; break; case "usertitles_id": $LEFTJOIN = $LEFTJOINUSER; $LEFTJOIN .= " LEFT JOIN `glpi_users`\n ON (`glpi_users`.`id` = `{$userlinktable}`.`users_id`)"; $WHERE .= " AND (`glpi_users`.`usertitles_id` = '{$value}'\n AND `{$userlinktable}`.`type` = '" . CommonITILActor::REQUESTER . "')"; break; case "usercategories_id": $LEFTJOIN = $LEFTJOINUSER; $LEFTJOIN .= " LEFT JOIN `glpi_users`\n ON (`glpi_users`.`id` = `{$userlinktable}`.`users_id`)"; $WHERE .= " AND (`glpi_users`.`usercategories_id` = '{$value}'\n AND `{$userlinktable}`.`type` = '" . CommonITILActor::REQUESTER . "')"; break; case "itilcategories_tree": if ($value == $value2) { $categories = array($value); } else { $categories = getSonsOf("glpi_itilcategories", $value); } $condition = implode("','", $categories); $WHERE .= " AND `{$table}`.`itilcategories_id` IN ('{$condition}')"; break; case 'locations_tree': if ($value == $value2) { $categories = array($value); } else { $categories = getSonsOf('glpi_locations', $value); } $condition = implode("','", $categories); $WHERE .= " AND `{$table}`.`locations_id` IN ('{$condition}')"; break; case 'group_tree': case 'groups_tree_assign': $grptype = $param == 'group_tree' ? CommonITILActor::REQUESTER : CommonITILActor::ASSIGN; if ($value == $value2) { $groups = array($value); } else { $groups = getSonsOf("glpi_groups", $value); } $condition = implode("','", $groups); $LEFTJOIN = $LEFTJOINGROUP; $WHERE .= " AND (`{$grouplinktable}`.`groups_id` IN ('{$condition}')\n AND `{$grouplinktable}`.`type` = '{$grptype}')"; break; case "group": $LEFTJOIN = $LEFTJOINGROUP; $WHERE .= " AND (`{$grouplinktable}`.`groups_id` = '{$value}'\n AND `{$grouplinktable}`.`type` = '" . CommonITILActor::REQUESTER . "')"; break; case "groups_id_assign": $LEFTJOIN = $LEFTJOINGROUP; $WHERE .= " AND (`{$grouplinktable}`.`groups_id` = '{$value}'\n AND `{$grouplinktable}`.`type` = '" . CommonITILActor::ASSIGN . "')"; break; case "suppliers_id_assign": $LEFTJOIN = $LEFTJOINSUPPLIER; $WHERE .= " AND (`{$supplierlinktable}`.`suppliers_id` = '{$value}'\n AND `{$supplierlinktable}`.`type` = '" . CommonITILActor::ASSIGN . "')"; break; case "requesttypes_id": case "solutiontypes_id": case "urgency": case "impact": case "priority": case "users_id_recipient": case "type": case "itilcategories_id": case 'locations_id': $WHERE .= " AND `{$table}`.`{$param}` = '{$value}'"; break; case "device": $devtable = getTableForItemType('Computer_' . $value2); $fkname = getForeignKeyFieldForTable(getTableForItemType($value2)); //select computers IDs that are using this device; $LEFTJOIN = ''; $linkdetable = $table; if ($itemtype == 'Ticket') { $linkedtable = 'glpi_items_tickets'; $LEFTJOIN .= " LEFT JOIN `glpi_items_tickets`\n ON (`glpi_tickets`.`id` = `glpi_items_tickets`.`tickets_id`)"; } $LEFTJOIN .= " INNER JOIN `glpi_computers`\n ON (`glpi_computers`.`id` = `{$linkedtable}`.`items_id`\n AND `{$linkedtable}`.`itemtype` = 'Computer')\n INNER JOIN `{$devtable}`\n ON (`glpi_computers`.`id` = `{$devtable}`.`computers_id`\n AND `{$devtable}`.`{$fkname}` = '{$value}')"; $WHERE .= " AND `glpi_computers`.`is_template` <> '1' "; break; case "comp_champ": $ftable = getTableForItemType($value2); $champ = getForeignKeyFieldForTable($ftable); $LEFTJOIN = ''; $linkdetable = $table; if ($itemtype == 'Ticket') { $linkedtable = 'glpi_items_tickets'; $LEFTJOIN .= " LEFT JOIN `glpi_items_tickets`\n ON (`glpi_tickets`.`id` = `glpi_items_tickets`.`tickets_id`)"; } $LEFTJOIN .= " INNER JOIN `glpi_computers`\n ON (`glpi_computers`.`id` = `{$linkedtable}`.`items_id`\n AND `{$linkedtable}`.`itemtype` = 'Computer')"; $WHERE .= " AND `glpi_computers`.`{$champ}` = '{$value}'\n AND `glpi_computers`.`is_template` <> '1'"; break; } switch ($type) { case "inter_total": $WHERE .= " AND " . getDateRequest("`{$table}`.`date`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`{$table}`.`date`),'%Y-%m')\n AS date_unix,\n COUNT(`{$table}`.`id`) AS total_visites\n FROM `{$table}`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `{$table}`.`date`"; break; case "inter_solved": $WHERE .= " AND `{$table}`.`status` IN ('" . implode("','", $solved_status) . "')\n AND `{$table}`.`solvedate` IS NOT NULL\n AND " . getDateRequest("`{$table}`.`solvedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`{$table}`.`solvedate`),'%Y-%m')\n AS date_unix,\n COUNT(`{$table}`.`id`) AS total_visites\n FROM `{$table}`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `{$table}`.`solvedate`"; break; case "inter_solved_late": $WHERE .= " AND `{$table}`.`status` IN ('" . implode("','", $solved_status) . "')\n AND `{$table}`.`solvedate` IS NOT NULL\n AND `{$table}`.`due_date` IS NOT NULL\n AND " . getDateRequest("`{$table}`.`solvedate`", $begin, $end) . "\n AND `{$table}`.`solvedate` > `{$table}`.`due_date`"; $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`{$table}`.`solvedate`),'%Y-%m')\n AS date_unix,\n COUNT(`{$table}`.`id`) AS total_visites\n FROM `{$table}`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `{$table}`.`solvedate`"; break; case "inter_closed": $WHERE .= " AND `{$table}`.`status` IN ('" . implode("','", $closed_status) . "')\n AND `{$table}`.`closedate` IS NOT NULL\n AND " . getDateRequest("`{$table}`.`closedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`{$table}`.`closedate`),'%Y-%m')\n AS date_unix,\n COUNT(`{$table}`.`id`) AS total_visites\n FROM `{$table}`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `{$table}`.`closedate`"; break; case "inter_avgsolvedtime": $WHERE .= " AND `{$table}`.`status` IN ('" . implode("','", $solved_status) . "')\n AND `{$table}`.`solvedate` IS NOT NULL\n AND " . getDateRequest("`{$table}`.`solvedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`{$table}`.`solvedate`),'%Y-%m')\n AS date_unix,\n AVG(solve_delay_stat) AS total_visites\n FROM `{$table}`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `{$table}`.`solvedate`"; break; case "inter_avgclosedtime": $WHERE .= " AND `{$table}`.`status` IN ('" . implode("','", $closed_status) . "')\n AND `{$table}`.`closedate` IS NOT NULL\n AND " . getDateRequest("`{$table}`.`closedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`{$table}`.`closedate`),'%Y-%m')\n AS date_unix,\n AVG(close_delay_stat) AS total_visites\n FROM `{$table}`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `{$table}`.`closedate`"; break; case "inter_avgactiontime": if ($param == "technicien_followup") { $actiontime_table = $tasktable; } else { $actiontime_table = $table; } $WHERE .= " AND `{$actiontime_table}`.`actiontime` > '0'\n AND " . getDateRequest("`{$table}`.`solvedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`{$table}`.`solvedate`),'%Y-%m')\n AS date_unix,\n AVG(`{$actiontime_table}`.`actiontime`) AS total_visites\n FROM `{$table}`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `{$table}`.`solvedate`"; break; case "inter_avgtakeaccount": $WHERE .= " AND `{$table}`.`status` IN ('" . implode("','", $solved_status) . "')\n AND `{$table}`.`solvedate` IS NOT NULL\n AND " . getDateRequest("`{$table}`.`solvedate`", $begin, $end); $query = "SELECT `{$table}`.`id`,\n FROM_UNIXTIME(UNIX_TIMESTAMP(`{$table}`.`solvedate`),'%Y-%m')\n AS date_unix,\n AVG(`{$table}`.`takeintoaccount_delay_stat`) AS total_visites\n FROM `{$table}`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `{$table}`.`solvedate`"; break; case "inter_opensatisfaction": $WHERE .= " AND `{$table}`.`status` IN ('" . implode("','", $closed_status) . "')\n AND `{$table}`.`closedate` IS NOT NULL\n AND " . getDateRequest("`{$table}`.`closedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`{$table}`.`closedate`),'%Y-%m')\n AS date_unix,\n COUNT(`{$table}`.`id`) AS total_visites\n FROM `{$table}`\n INNER JOIN `glpi_ticketsatisfactions`\n ON (`{$table}`.`id` = `glpi_ticketsatisfactions`.`tickets_id`)\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `{$table}`.`closedate`"; break; case "inter_answersatisfaction": $WHERE .= " AND `{$table}`.`status` IN ('" . implode("','", $closed_status) . "')\n AND `{$table}`.`closedate` IS NOT NULL\n AND `glpi_ticketsatisfactions`.`date_answered` IS NOT NULL\n AND " . getDateRequest("`{$table}`.`closedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`{$table}`.`closedate`),'%Y-%m')\n AS date_unix,\n COUNT(`{$table}`.`id`) AS total_visites\n FROM `{$table}`\n INNER JOIN `glpi_ticketsatisfactions`\n ON (`{$table}`.`id` = `glpi_ticketsatisfactions`.`tickets_id`)\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `{$table}`.`closedate`"; break; case "inter_avgsatisfaction": $WHERE .= " AND `glpi_ticketsatisfactions`.`date_answered` IS NOT NULL\n AND `{$table}`.`status` IN ('" . implode("','", $closed_status) . "')\n AND `{$table}`.`closedate` IS NOT NULL\n AND " . getDateRequest("`{$table}`.`closedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`{$table}`.`closedate`),'%Y-%m')\n AS date_unix,\n AVG(`glpi_ticketsatisfactions`.`satisfaction`) AS total_visites\n FROM `{$table}`\n INNER JOIN `glpi_ticketsatisfactions`\n ON (`{$table}`.`id` = `glpi_ticketsatisfactions`.`tickets_id`)\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `{$table}`.`closedate`"; break; } $entrees = array(); $count = array(); if (empty($query)) { return array(); } $result = $DB->query($query); if ($result && $DB->numrows($result) > 0) { while ($row = $DB->fetch_assoc($result)) { $date = $row['date_unix']; //$visites = round($row['total_visites']); $entrees["{$date}"] = $row['total_visites']; } } // Remplissage de $entrees pour les mois ou il n'y a rien // $min=-1; // $max=0; // if (count($entrees)==0) { // return $entrees; // } // foreach ($entrees as $key => $val) { // $time=strtotime($key."-01"); // if ($min>$time || $min<0) { // $min=$time; // } // if ($max<$time) { // $max=$time; // } // } $end_time = strtotime(date("Y-m", strtotime($end)) . "-01"); $begin_time = strtotime(date("Y-m", strtotime($begin)) . "-01"); // if ($max<$end_time) { // $max=$end_time; // } // if ($min>$begin_time) { // $min=$begin_time; // } $current = $begin_time; while ($current <= $end_time) { $curentry = date("Y-m", $current); if (!isset($entrees["{$curentry}"])) { $entrees["{$curentry}"] = 0; } $month = date("m", $current); $year = date("Y", $current); $current = mktime(0, 0, 0, intval($month) + 1, 1, intval($year)); } ksort($entrees); return $entrees; }
/** Get recipient of tickets between 2 dates * * @param $date1 date : begin date * @param $date2 date : end date * @param title : indicates if stat if by title (true) or type (false) * * @return array contains the distinct recipents which have tickets **/ function getUsedUserTitleOrTypeBetween($date1 = '', $date2 = '', $title = true) { global $DB; if ($title) { $table = "glpi_usertitles"; $field = "usertitles_id"; } else { $table = "glpi_usercategories"; $field = "usercategories_id"; } $query = "SELECT DISTINCT `glpi_users`.`{$field}`\n FROM `glpi_tickets`\n INNER JOIN `glpi_tickets_users`\n ON (`glpi_tickets`.`id` = `glpi_tickets_users`.`tickets_id`\n AND `glpi_tickets_users`.`type` = '" . CommonITILActor::REQUESTER . "')\n INNER JOIN `glpi_users` ON (`glpi_users`.`id` = `glpi_tickets_users`.`users_id`)\n LEFT JOIN `{$table}` ON (`{$table}`.`id` = `glpi_users`.`{$field}`) " . getEntitiesRestrictRequest("WHERE", "glpi_tickets"); if (!empty($date1) || !empty($date2)) { $query .= " AND (" . getDateRequest("`glpi_tickets`.`date`", $date1, $date2) . "\n OR " . getDateRequest("`glpi_tickets`.`closedate`", $date1, $date2) . ") "; } $query .= " ORDER BY `glpi_users`.`{$field}`"; $result = $DB->query($query); $tab = array(); if ($DB->numrows($result) >= 1) { while ($line = $DB->fetch_assoc($result)) { $tmp['id'] = $line[$field]; $tmp['link'] = Dropdown::getDropdownName($table, $line[$field]); $tab[] = $tmp; } } return $tab; }
/** * Show printer graph form **/ function showGraph($id, $options = array()) { global $DB, $CFG_GLPI; $printer = new Printer(); $where = ''; $begin = ''; $end = ''; $timeUnit = 'day'; $graphField = 'pages_total'; $pagecounters = array(); $graphType = 'day'; if (isset($_SESSION['glpi_plugin_fusioninventory_graph_begin'])) { $begin = $_SESSION['glpi_plugin_fusioninventory_graph_begin']; } if ($begin == 'NULL' or $begin == '') { $begin = date("Y-m-01"); // first day of current month } if (isset($_SESSION['glpi_plugin_fusioninventory_graph_end'])) { $end = $_SESSION['glpi_plugin_fusioninventory_graph_end']; } if (isset($_SESSION['glpi_plugin_fusioninventory_graph_type'])) { $graphType = $_SESSION['glpi_plugin_fusioninventory_graph_type']; } if ($end == 'NULL' or $end == '') { $end = date("Y-m-d"); // today } if (isset($_SESSION['glpi_plugin_fusioninventory_graph_timeUnit'])) { $timeUnit = $_SESSION['glpi_plugin_fusioninventory_graph_timeUnit']; } if (!isset($_SESSION['glpi_plugin_fusioninventory_graph_printersComp'])) { $_SESSION['glpi_plugin_fusioninventory_graph_printersComp'] = array(); } if (isset($_SESSION['glpi_plugin_fusioninventory_graph_printerCompAdd'])) { $printerCompAdd = $_SESSION['glpi_plugin_fusioninventory_graph_printerCompAdd']; if (!key_exists($printerCompAdd, $_SESSION['glpi_plugin_fusioninventory_graph_printersComp'])) { $oPrinter = new Printer(); if ($oPrinter->getFromDB($printerCompAdd)) { $_SESSION['glpi_plugin_fusioninventory_graph_printersComp'][$printerCompAdd] = $oPrinter->getField('name'); } } } elseif (isset($_SESSION['glpi_plugin_fusioninventory_graph_printerCompRemove'])) { unset($_SESSION['glpi_plugin_fusioninventory_graph_printersComp'][$_SESSION['glpi_plugin_fusioninventory_graph_printerCompRemove']]); } $oPrinter = new Printer(); $printers = $_SESSION['glpi_plugin_fusioninventory_graph_printersComp']; $printersView = $printers; // printers without the current printer if (isset($printersView[$id])) { unset($printersView[$id]); } else { if ($oPrinter->getFromDB($id)) { $printers[$id] = $oPrinter->getField('name'); } } $printersList = ''; foreach ($printers as $printers_id => $printername) { if ($printersList != '') { $printersList .= '<br/>'; } if ($printers_id == $id) { $printersList .= $printername; } else { $oPrinter->getFromDB($printers_id); $printersList .= $oPrinter->getLink(1); } } $printersIds = ""; foreach (array_keys($printers) as $printerId) { if ($printersIds != '') { $printersIds .= ', '; } $printersIds .= $printerId; } $where = " WHERE `printers_id` IN(" . $printersIds . ")"; if ($begin != '' || $end != '') { $where .= " AND " . getDateRequest("`date`", $begin, $end); } $group = ''; switch ($timeUnit) { case 'day': $group = "GROUP BY `printers_id`, `year`, `month`, `day`"; break; case 'week': $group = "GROUP BY `printers_id`, `year`, `month`, `week`"; break; case 'month': $group = "GROUP BY `printers_id`, `year`, `month`"; break; case 'year': $group = "GROUP BY `printers_id`, `year`"; break; } echo "<form method='post' name='snmp_form' id='snmp_form' action='" . $CFG_GLPI['root_doc'] . "/plugins/fusioninventory/front/printer_info.form.php'>"; echo "<table class='tab_cadre' cellpadding='5' width='950'>"; $mapping = new PluginFusioninventoryMapping(); $maps = $mapping->find("`itemtype`='Printer'"); foreach ($maps as $mapfields) { if (!isset($mapfields["shortlocale"])) { $mapfields["shortlocale"] = $mapfields["locale"]; } $pagecounters[$mapfields['name']] = $mapping->getTranslation($mapfields); } echo "<tr class='tab_bg_1'>"; echo "<th colspan='4'>"; echo __('Printed page counter', 'fusioninventory'); echo "</th>"; echo "</tr>"; echo "<tr class='tab_bg_1'>"; echo "<td class='left'>" . __('Start date') . " :</td>"; echo "<td class='left'>"; Html::showDateFormItem("graph_begin", $begin); echo "</td>"; echo "<td class='left'>" . __('Time unit', 'fusioninventory') . " :</td>"; echo "<td class='left'>"; $elementsTime = array('day' => _n('Day', 'Days', 1), 'week' => __('Week'), 'month' => _n('Month', 'Months', 1), 'year' => __('Year', 'fusioninventory')); Dropdown::showFromArray('graph_timeUnit', $elementsTime, array('value' => $timeUnit)); echo "</td>"; echo "</tr>"; echo "<tr class='tab_bg_1'>"; echo "<td class='left'>" . __('End date') . " :</td>"; echo "<td class='left'>"; Html::showDateFormItem("graph_end", $end); echo "</td>"; echo "<td class='left'>" . __('Display', 'fusioninventory') . " :</td>"; echo "<td class='left'>"; $elements = array('total' => __('Total counter', 'fusioninventory'), 'day' => __('pages per day', 'fusioninventory')); Dropdown::showFromArray('graph_type', $elements, array('value' => $graphType)); echo "</td>"; echo "</tr>"; echo "<tr class='tab_bg_2'>"; echo "<td class='center' colspan='4'>\n <input type='submit' class='submit' name='graph_plugin_fusioninventory_printer_period'\n value='" . __('Update') . "'/>"; echo "</td>"; echo "</tr>\n"; echo "<tr>"; echo "<th colspan='4'>" . __('Printers to compare', 'fusioninventory') . "</th>"; echo "</tr>"; echo "<tr class='tab_bg_1'>"; echo "<td class='left' rowspan='3'>" . __('Printer') . " :</td>"; echo "<td class='left' rowspan='3'>"; echo $printersList; echo "</td>"; echo "<td class='left'>" . __('Add a printer', 'fusioninventory') . " :</td>"; echo "<td class='left'>"; $printersused = array(); foreach ($printersView as $printer_id => $name) { $printersused[] = $printer_id; } $printer->getFromDB($id); Dropdown::show('Printer', array('name' => 'graph_printerCompAdd', 'entity' => $printer->fields['entities_id'], 'used' => $printersused)); echo " <input type='submit' value=\"" . __('Add') . "\" class='submit' " . "name='graph_plugin_fusioninventory_printer_add'>"; echo "</td>"; echo "</tr>\n"; echo "<tr class='tab_bg_1'>"; echo "<td class='left'>" . __('Remove a printer', 'fusioninventory') . " :</td>"; echo "<td class='left'>"; $printersTmp = $printersView; $printersTmp[0] = "-----"; asort($printersTmp); Dropdown::showFromArray('graph_printerCompRemove', $printersTmp); echo " <input type='submit' value=\"" . __('Delete', 'fusioninventory') . "\" " . "class='submit' name='graph_plugin_fusioninventory_printer_remove'>"; echo "</td>"; echo "</tr>\n"; echo "<tr class='tab_bg_1'>"; echo "<td colspan='2'></td>"; echo "</tr>"; echo "</table>"; Html::closeForm(); $elementsField = array('pages_total' => $pagecounters['pagecountertotalpages'], 'pages_n_b' => $pagecounters['pagecounterblackpages'], 'pages_color' => $pagecounters['pagecountercolorpages'], 'pages_recto_verso' => $pagecounters['pagecounterrectoversopages'], 'scanned' => $pagecounters['pagecounterscannedpages'], 'pages_total_print' => $pagecounters['pagecountertotalpages_print'], 'pages_n_b_print' => $pagecounters['pagecounterblackpages_print'], 'pages_color_print' => $pagecounters['pagecountercolorpages_print'], 'pages_total_copy' => $pagecounters['pagecountertotalpages_copy'], 'pages_n_b_copy' => $pagecounters['pagecounterblackpages_copy'], 'pages_color_copy' => $pagecounters['pagecountercolorpages_copy'], 'pages_total_fax' => $pagecounters['pagecountertotalpages_fax']); echo "<br/>"; $a_graph = array(); foreach ($elementsField as $graphField => $name) { $query = "SELECT `printers_id`, DAY(`date`)-1 AS `day`, WEEK(`date`) AS `week`,\n MONTH(`date`) AS `month`, YEAR(`date`) AS `year`, `date`,\n `{$graphField}`\n FROM `glpi_plugin_fusioninventory_printerlogs`" . $where . " AND `" . $graphField . "` > 0 " . $group; $result = $DB->query($query); if ($DB->numrows($result) == 0) { unset($elementsField[$graphField]); } } foreach ($elementsField as $graphField => $name) { $query = "SELECT `printers_id`, DAY(`date`)-1 AS `day`, WEEK(`date`) AS `week`,\n MONTH(`date`) AS `month`, YEAR(`date`) AS `year`, `date`,\n `{$graphField}`\n FROM `glpi_plugin_fusioninventory_printerlogs`" . $where . $group . "\n ORDER BY `year`, `month`, `day`, `printers_id`"; $input = array(); $result = $DB->query($query); if ($result) { if ($DB->numrows($result) != 0) { $pages = array(); $data = array(); $date = ''; while ($data = $DB->fetch_assoc($result)) { switch ($timeUnit) { case 'day': $split = explode(" ", $data['date']); $date = $split[0]; break; case 'week': $split = explode(" ", $data['date']); $date = $split[0]; break; case 'month': $split = explode(" ", $data['date']); $split2 = explode("-", $split[0]); $date = $split2[0] . "-" . $split2[1]; break; case 'year': $split = explode(" ", $data['date']); $split2 = explode("-", $split[0]); $date = $split2[0]; break; } if ($graphType == 'day') { if (!isset($pages[$data['printers_id']])) { $pages[$data['printers_id']] = $data[$graphField]; } else { $y = $data[$graphField] - $pages[$data['printers_id']]; if ($y < 0) { $y = 0; } $input[] = array('x' => $date, 'y' => $y); if ($data[$graphField] > 0) { $pages[$data['printers_id']] = $data[$graphField]; } } } else { $input[] = array('x' => $date, 'y' => $data[$graphField]); } } } } $continue = 1; if ($continue == '0' or $continue == '-1') { echo "<table class='tab_cadre' cellpadding='5' width='900'>"; echo "<tr class='tab_bg_1'>"; echo "<th>"; echo $name; echo "</th>"; echo "</tr>"; echo "<tr class='tab_bg_1'>"; echo "<td align='center'>"; if ($continue == '0') { echo __('Too datas to display', 'fusioninventory'); } echo "</td>"; echo "</tr>"; echo "</table><br/>"; } else { if (count($input > 0)) { $split = explode(' > ', $name); $a_graph[] = array('key' => $split[count($split) - 1], 'values' => $input); } } } // Display graph echo '<div id="chartPrinter">' . '<svg style="height: 400px; width: 950px;"></svg>' . '</div>'; echo "<script type='text/javascript'>\n function drawGraph() {\n var chart = nv.models.multiBarChart();\n\n chart.yAxis\n .tickFormat(d3.format(',0f'));\n\n d3.select('#chartPrinter svg')\n .datum(exampleData())\n .transition().duration(500).call(chart);\n\n nv.utils.windowResize(chart.update);\n }\n "; echo ' function exampleData() { return ' . json_encode($a_graph) . ' } drawGraph(); </script>'; }
/** Get groups assigned to itil object between 2 dates * * @param $date1 date : begin date (default '') * @param $date2 date : end date (default '') * * @return array contains the distinct groups assigned to a tickets **/ function getUsedAssignGroupBetween($date1 = '', $date2 = '') { global $DB; $linkclass = new $this->grouplinkclass(); $linktable = $linkclass->getTable(); $query = "SELECT DISTINCT `glpi_groups`.`id`, `glpi_groups`.`completename`\n FROM `" . $this->getTable() . "`\n LEFT JOIN `{$linktable}`\n ON (`{$linktable}`.`" . $this->getForeignKeyField() . "` = `" . $this->getTable() . "`.`id`\n AND `{$linktable}`.`type` = '" . CommonITILActor::ASSIGN . "')\n LEFT JOIN `glpi_groups` ON (`{$linktable}`.`groups_id` = `glpi_groups`.`id`)\n WHERE NOT `" . $this->getTable() . "`.`is_deleted` " . getEntitiesRestrictRequest("AND", $this->getTable()); if (!empty($date1) || !empty($date2)) { $query .= " AND (" . getDateRequest("`" . $this->getTable() . "`.`date`", $date1, $date2) . "\n OR " . getDateRequest("`" . $this->getTable() . "`.`closedate`", $date1, $date2) . ") "; } $query .= " ORDER BY `glpi_groups`.`completename`"; $result = $DB->query($query); $tab = array(); if ($DB->numrows($result) >= 1) { while ($line = $DB->fetch_assoc($result)) { $tmp['id'] = $line["id"]; $tmp['link'] = $line["completename"]; $tab[] = $tmp; } } return $tab; }
static function constructEntryValues($type, $begin = "", $end = "", $param = "", $value = "", $value2 = "") { global $DB; $query = ""; $WHERE = getEntitiesRestrictRequest("WHERE", "glpi_tickets"); $LEFTJOIN = ""; $LEFTJOINUSER = "******"; $LEFTJOINGROUP = "LEFT JOIN `glpi_groups_tickets`\n ON (`glpi_groups_tickets`.`tickets_id` = `glpi_tickets`.`id`)"; switch ($param) { case "technicien": $LEFTJOIN = $LEFTJOINUSER; $WHERE .= " AND (`glpi_tickets_users`.`users_id` = '{$value}'\n AND `glpi_tickets_users`.`type`='" . Ticket::ASSIGN . "')"; break; case "technicien_followup": $WHERE .= " AND `glpi_tickettasks`.`users_id` = '{$value}'"; $LEFTJOIN = " LEFT JOIN `glpi_tickettasks`\n ON (`glpi_tickettasks`.`tickets_id` = `glpi_tickets`.`id`)"; break; case "enterprise": $WHERE .= " AND `glpi_tickets`.`suppliers_id_assign` = '{$value}'"; break; case "user": $LEFTJOIN = $LEFTJOINUSER; $WHERE .= " AND (`glpi_tickets_users`.`users_id` = '{$value}'\n AND `glpi_tickets_users`.`type` ='" . Ticket::REQUESTER . "')"; break; case "usertitles_id": $LEFTJOIN = $LEFTJOINUSER; $LEFTJOIN .= " LEFT JOIN `glpi_users`\n ON (`glpi_users`.`id` = `glpi_tickets_users`.`users_id`)"; $WHERE .= " AND (`glpi_users`.`usertitles_id` = '{$value}'\n AND `glpi_tickets_users`.`type` = '" . Ticket::REQUESTER . "')"; break; case "usercategories_id": $LEFTJOIN = $LEFTJOINUSER; $LEFTJOIN .= " LEFT JOIN `glpi_users`\n ON (`glpi_users`.`id` = `glpi_tickets_users`.`users_id`)"; $WHERE .= " AND (`glpi_users`.`usercategories_id` = '{$value}'\n AND `glpi_tickets_users`.`type` = '" . Ticket::REQUESTER . "')"; break; case "users_id_recipient": $WHERE .= " AND `glpi_tickets`.`users_id_recipient` = '{$value}'"; break; case "type": $WHERE .= " AND `glpi_tickets`.`type` = '{$value}'"; break; case "ticketcategories_id": if (!empty($value)) { // do not merge for pie chart if (!isset($_REQUEST['showgraph']) || !$_REQUEST['showgraph']) { $categories = getSonsOf("glpi_ticketcategories", $value); $condition = implode("','", $categories); $WHERE .= " AND `glpi_tickets`.`ticketcategories_id` IN ('{$condition}')"; } else { $WHERE .= " AND `glpi_tickets`.`ticketcategories_id` = '{$value}' "; } } else { $WHERE .= " AND `glpi_tickets`.`ticketcategories_id` = '{$value}' "; } break; case "group": $LEFTJOIN = $LEFTJOINGROUP; $WHERE .= " AND (`glpi_groups_tickets`.`groups_id` = '{$value}'\n AND `glpi_groups_tickets`.`type` = '" . Ticket::REQUESTER . "')"; break; case "groups_id_assign": $LEFTJOIN = $LEFTJOINGROUP; $WHERE .= " AND (`glpi_groups_tickets`.`groups_id` = '{$value}'\n AND `glpi_groups_tickets`.`type` = '" . Ticket::ASSIGN . "')"; break; case "requesttypes_id": case "ticketsolutiontypes_id": case "urgency": case "impact": case "priority": $WHERE .= " AND `glpi_tickets`.`{$param}` = '{$value}'"; break; case "device": $devtable = getTableForItemType('Computer_' . $value2); $fkname = getForeignKeyFieldForTable(getTableForItemType($value2)); //select computers IDs that are using this device; $LEFTJOIN = " INNER JOIN `glpi_computers`\n ON (`glpi_computers`.`id` = `glpi_tickets`.`items_id`\n AND `glpi_tickets`.`itemtype` = 'Computer')\n INNER JOIN `{$devtable}`\n ON (`glpi_computers`.`id` = `{$devtable}`.`computers_id`\n AND `{$devtable}`.`{$fkname}` = '{$value}')"; $WHERE .= " AND `glpi_computers`.`is_template` <> '1' "; break; case "comp_champ": $table = getTableForItemType($value2); $champ = getForeignKeyFieldForTable($table); $LEFTJOIN = " INNER JOIN `glpi_computers`\n ON (`glpi_computers`.`id` = `glpi_tickets`.`items_id`\n AND `glpi_tickets`.`itemtype` = 'Computer')"; $WHERE .= " AND `glpi_computers`.`{$champ}` = '{$value}'\n AND `glpi_computers`.`is_template` <> '1'"; break; } switch ($type) { case "inter_total": $WHERE .= " AND " . getDateRequest("`glpi_tickets`.`date`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`glpi_tickets`.`date`),'%Y-%m')\n AS date_unix,\n COUNT(`glpi_tickets`.`id`) AS total_visites\n FROM `glpi_tickets`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `glpi_tickets`.`date`"; break; case "inter_solved": $WHERE .= " AND (`glpi_tickets`.`status` = 'closed'\n OR `glpi_tickets`.`status` = 'solved')\n AND `glpi_tickets`.`solvedate` IS NOT NULL\n AND " . getDateRequest("`glpi_tickets`.`solvedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`glpi_tickets`.`solvedate`),'%Y-%m')\n AS date_unix,\n COUNT(`glpi_tickets`.`id`) AS total_visites\n FROM `glpi_tickets`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `glpi_tickets`.`solvedate`"; break; case "inter_solved_late": $WHERE .= " AND (`glpi_tickets`.`status` = 'closed'\n OR `glpi_tickets`.`status` = 'solved')\n AND `glpi_tickets`.`solvedate` IS NOT NULL\n AND `glpi_tickets`.`due_date` IS NOT NULL\n AND " . getDateRequest("`glpi_tickets`.`solvedate`", $begin, $end) . "\n AND `glpi_tickets`.`solvedate` > `glpi_tickets`.`due_date`"; $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`glpi_tickets`.`solvedate`),'%Y-%m')\n AS date_unix,\n COUNT(`glpi_tickets`.`id`) AS total_visites\n FROM `glpi_tickets`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `glpi_tickets`.`solvedate`"; break; case "inter_closed": $WHERE .= " AND (`glpi_tickets`.`status` = 'closed')\n AND `glpi_tickets`.`closedate` IS NOT NULL\n AND " . getDateRequest("`glpi_tickets`.`closedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`glpi_tickets`.`closedate`),'%Y-%m')\n AS date_unix,\n COUNT(`glpi_tickets`.`id`) AS total_visites\n FROM `glpi_tickets`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `glpi_tickets`.`closedate`"; break; case "inter_avgsolvedtime": $WHERE .= " AND (`glpi_tickets`. `status` = 'solved'\n OR `glpi_tickets`.`status` = 'closed')\n AND `glpi_tickets`.`solvedate` IS NOT NULL\n AND " . getDateRequest("`glpi_tickets`.`solvedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`glpi_tickets`.`solvedate`),'%Y-%m')\n AS date_unix,\n AVG(solve_delay_stat) AS total_visites\n FROM `glpi_tickets`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `glpi_tickets`.`solvedate`"; break; case "inter_avgclosedtime": $WHERE .= " AND (`glpi_tickets`.`status` = 'closed')\n AND `glpi_tickets`.`closedate` IS NOT NULL\n AND " . getDateRequest("`glpi_tickets`.`closedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`glpi_tickets`.`closedate`),'%Y-%m')\n AS date_unix,\n AVG(close_delay_stat) AS total_visites\n FROM `glpi_tickets`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `glpi_tickets`.`closedate`"; break; case "inter_avgactiontime": if ($param == "technicien_followup") { $actiontime_table = "glpi_tickettasks"; } else { $actiontime_table = "glpi_tickets"; } $WHERE .= " AND `{$actiontime_table}`.`actiontime` > '0'\n AND " . getDateRequest("`glpi_tickets`.`solvedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`glpi_tickets`.`solvedate`),'%Y-%m')\n AS date_unix,\n AVG(`{$actiontime_table}`.`actiontime`) AS total_visites\n FROM `glpi_tickets`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `glpi_tickets`.`solvedate`"; break; case "inter_avgtakeaccount": $WHERE .= " AND (`glpi_tickets`.`status` = 'solved'\n OR `glpi_tickets`.`status` = 'closed')\n AND `glpi_tickets`.`solvedate` IS NOT NULL\n AND " . getDateRequest("`glpi_tickets`.`solvedate`", $begin, $end); $query = "SELECT `glpi_tickets`.`id`,\n FROM_UNIXTIME(UNIX_TIMESTAMP(`glpi_tickets`.`solvedate`),'%Y-%m')\n AS date_unix,\n AVG(`glpi_tickets`.`takeintoaccount_delay_stat`) AS total_visites\n FROM `glpi_tickets`\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY `glpi_tickets`.`id`\n ORDER BY `glpi_tickets`.`solvedate`"; break; case "inter_opensatisfaction": $WHERE .= " AND `glpi_tickets`.`status` = 'closed'\n AND `glpi_tickets`.`closedate` IS NOT NULL\n AND " . getDateRequest("`glpi_tickets`.`closedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`glpi_tickets`.`closedate`),'%Y-%m')\n AS date_unix,\n COUNT(`glpi_tickets`.`id`) AS total_visites\n FROM `glpi_tickets`\n INNER JOIN `glpi_ticketsatisfactions`\n ON (`glpi_tickets`.`id` = `glpi_ticketsatisfactions`.`tickets_id`)\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `glpi_tickets`.`closedate`"; break; case "inter_answersatisfaction": $WHERE .= " AND `glpi_tickets`.`status` = 'closed'\n AND `glpi_tickets`.`closedate` IS NOT NULL\n AND `glpi_ticketsatisfactions`.`date_answered` IS NOT NULL\n AND " . getDateRequest("`glpi_tickets`.`closedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`glpi_tickets`.`closedate`),'%Y-%m')\n AS date_unix,\n COUNT(`glpi_tickets`.`id`) AS total_visites\n FROM `glpi_tickets`\n INNER JOIN `glpi_ticketsatisfactions`\n ON (`glpi_tickets`.`id` = `glpi_ticketsatisfactions`.`tickets_id`)\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `glpi_tickets`.`closedate`"; break; case "inter_avgsatisfaction": $WHERE .= " AND `glpi_ticketsatisfactions`.`date_answered` IS NOT NULL\n AND `glpi_tickets`.`status` = 'closed'\n AND `glpi_tickets`.`closedate` IS NOT NULL\n AND " . getDateRequest("`glpi_tickets`.`closedate`", $begin, $end); $query = "SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`glpi_tickets`.`closedate`),'%Y-%m')\n AS date_unix,\n AVG(`glpi_ticketsatisfactions`.`satisfaction`) AS total_visites\n FROM `glpi_tickets`\n INNER JOIN `glpi_ticketsatisfactions`\n ON (`glpi_tickets`.`id` = `glpi_ticketsatisfactions`.`tickets_id`)\n {$LEFTJOIN}\n {$WHERE}\n GROUP BY date_unix\n ORDER BY `glpi_tickets`.`closedate`"; break; } $entrees = array(); $count = array(); if (empty($query)) { return array(); } $result = $DB->query($query); if ($result && $DB->numrows($result) > 0) { while ($row = $DB->fetch_array($result)) { $date = $row['date_unix']; //$visites = round($row['total_visites']); $entrees["{$date}"] = $row['total_visites']; } } // Remplissage de $entrees pour les mois ou il n'y a rien // $min=-1; // $max=0; // if (count($entrees)==0) { // return $entrees; // } // foreach ($entrees as $key => $val) { // $time=strtotime($key."-01"); // if ($min>$time || $min<0) { // $min=$time; // } // if ($max<$time) { // $max=$time; // } // } $end_time = strtotime(date("Y-m", strtotime($end)) . "-01"); $begin_time = strtotime(date("Y-m", strtotime($begin)) . "-01"); // if ($max<$end_time) { // $max=$end_time; // } // if ($min>$begin_time) { // $min=$begin_time; // } $current = $begin_time; while ($current <= $end_time) { $curentry = date("Y-m", $current); if (!isset($entrees["{$curentry}"])) { $entrees["{$curentry}"] = 0; } $month = date("m", $current); $year = date("Y", $current); $current = mktime(0, 0, 0, intval($month) + 1, 1, intval($year)); } ksort($entrees); return $entrees; }