// $output_type = Search::HTML_OUTPUT; //} //Report title $title = $LANG['plugin_typology']['typologyreport']; $styleItemTitle = 'font-size: 12px; font-weight: bold; background-color: #e1cc7b; text-align: center; -moz-border-radius: 4px; -webkit-border-radius: 4px; -o-border-radius: 4px; padding: 2px;'; //to verify if typology exist in this entity // SQL statement $condition = getEntitiesRestrictRequest('', "glpi_plugin_typology_typologies", '', '', true); $sqltypo = $typocrit->getSqlCriteriasRestriction('AND'); $query = "SELECT *\n FROM `glpi_plugin_typology_typologies`\n WHERE {$condition} {$sqltypo} "; $res = $DB->query($query); $nbtot = $res ? $DB->numrows($res) : 0; if ($limit) { $start = isset($_GET["start"]) ? $_GET["start"] : 0; if ($start >= $nbtot) { $start = 0; } if ($start > 0 || $start + $limit < $nbtot) { $res = $DB->query($query . " LIMIT {$start},{$limit}"); } } else { $start = 0; } if ($nbtot == 0) {
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with reports. If not, see <http://www.gnu.org/licenses/>. -------------------------------------------------------------------------- */ // Options for GLPI 0.71 and newer : need slave db to access the report $USEDBREPLICATE = 1; $DBCONNECTION_REQUIRED = 1; // Initialization of the variables include "../../../../inc/includes.php"; //TRANS: The name of the report = Helpdesk requesters and tickets by entity $report = new PluginReportsAutoReport(__('statticketsbyentity_report_title', 'reports')); //Report's search criterias $prof = new PluginReportsDropdownCriteria($report, 'profiles_id', 'glpi_profiles', __('Profile')); //Display criterias form is needed $report->displayCriteriasForm(); //If criterias have been validated if ($report->criteriasValidated()) { $report->setSubNameAuto(); //Names of the columns to be displayed $cols = array(new PluginReportsColumn('name', __('Entity'), array('sorton' => '`glpi_entities`.`completename`')), new PluginReportsColumnInteger('nbusers', __('Users count', 'reports'), array('withtotal' => true, 'sorton' => 'nbusers')), new PluginReportsColumnInteger('number', __('Tickets count', 'reports'), array('withtotal' => true, 'sorton' => 'number')), new PluginReportsColumnDateTime('mindate', __('Older', 'reports'), array('sorton' => 'mindate')), new PluginReportsColumnDateTime('maxdate', __('Newer', 'reports'), array('sorton' => 'maxdate'))); $report->setColumns($cols); $subcpt = "SELECT COUNT(*)\n FROM `glpi_profiles_users`\n WHERE `glpi_profiles_users`.`entities_id`=`glpi_entities`.`id` " . $prof->getSqlCriteriasRestriction(); $query = "SELECT `glpi_entities`.`completename` AS name,\n ({$subcpt}) as nbusers,\n COUNT(`glpi_tickets`.`id`) AS number,\n MIN(`glpi_tickets`.`date`) as mindate,\n MAX(`glpi_tickets`.`date`) as maxdate\n FROM `glpi_entities`\n INNER JOIN `glpi_tickets` ON (`glpi_tickets`.`entities_id`=`glpi_entities`.`id`)\n WHERE NOT `glpi_tickets`.`is_deleted` " . getEntitiesRestrictRequest('AND', "glpi_entities") . "GROUP BY `glpi_entities`.`id`" . $report->getOrderBy('name'); $report->setSqlRequest($query); $report->execute(array('withtotal' => true)); } else { Html::footer(); }
if (isset($_POST["display_type"])) { $output_type = $_POST["display_type"]; if ($output_type < 0) { $output_type = -$output_type; $limit = 0; } } else { $output_type = Search::HTML_OUTPUT; } $title = $report->getFullTitle(); //to verify if professions exist // SQL statement $condition = getEntitiesRestrictRequest('AND', 'glpi_plugin_resources_resources'); $date = date("Y-m-d"); $sqlprofessioncategory = $professioncategory->getSqlCriteriasRestriction('AND'); $sqlprofessionline = $professionline->getSqlCriteriasRestriction('AND'); $dataAll = array(); //recover all professions_id present in resources $requestResource = "SELECT DISTINCT(`glpi_plugin_resources_ranks`.`plugin_resources_professions_id`)\n FROM `glpi_plugin_resources_resources`\n LEFT JOIN `glpi_plugin_resources_ranks`\n ON (`glpi_plugin_resources_ranks`.`id` = `glpi_plugin_resources_resources`.`plugin_resources_ranks_id`)\n WHERE `glpi_plugin_resources_resources`.`date_begin` <= '" . $date . "'\n AND (`glpi_plugin_resources_resources`.`date_end` IS NULL\n OR `glpi_plugin_resources_resources`.`date_end` >= '" . $date . "')" . $condition . "ORDER BY `plugin_resources_professions_id`"; $professionsResourceList = '0'; foreach ($DB->request($requestResource) as $data) { if ($data['plugin_resources_professions_id'] != NULL) { $professionsResourceList .= ','; $professionsResourceList .= $data['plugin_resources_professions_id']; } } //case global management $query = "SELECT `glpi_plugin_resources_professions`.`plugin_resources_professionlines_id` AS professionline,\n `glpi_plugin_resources_professions`.`plugin_resources_professioncategories_id` AS professioncategory,\n `glpi_plugin_resources_professions`.`name` AS profession,\n `glpi_plugin_resources_professions`.`id` AS profession_id,\n `glpi_plugin_resources_professions`.`code` AS profession_code,\n 0 AS rank_name, 0 AS rank_code,\n `glpi_plugin_resources_professions`.`begin_date`,\n `glpi_plugin_resources_professions`.`end_date`\n FROM `glpi_plugin_resources_professions`\n LEFT JOIN `glpi_plugin_resources_budgets`\n ON (`glpi_plugin_resources_budgets`.`plugin_resources_professions_id` = `glpi_plugin_resources_professions`.`id`\n AND ((`glpi_plugin_resources_budgets`.`begin_date` IS NULL)\n OR (`glpi_plugin_resources_budgets`.`begin_date` < '" . $date . "')\n AND (`glpi_plugin_resources_budgets`.`end_date` IS NULL)\n OR (`glpi_plugin_resources_budgets`.`end_date` > '" . $date . "')))\n WHERE (`glpi_plugin_resources_professions`.`id` IN (" . $professionsResourceList . ")\n AND `glpi_plugin_resources_professions`.`is_active` = 1\n AND ((`glpi_plugin_resources_professions`.`end_date` IS NULL )\n OR (`glpi_plugin_resources_professions`.`end_date` > '" . $date . "' ))\n AND ((`glpi_plugin_resources_professions`.`begin_date` IS NULL)\n OR ( `glpi_plugin_resources_professions`.`begin_date` < '" . $date . "')))\n AND ((`glpi_plugin_resources_budgets`.`id` IS NULL)\n OR (`glpi_plugin_resources_budgets`.`begin_date` IS NOT NULL\n AND `glpi_plugin_resources_budgets`.`begin_date` > '" . $date . "')\n OR (`glpi_plugin_resources_budgets`.`end_date` IS NOT NULL\n AND `glpi_plugin_resources_budgets`.`end_date` < '" . $date . "')) " . $sqlprofessioncategory . $sqlprofessionline; $conditionAll = getEntitiesRestrictRequest('AND', 'glpi_plugin_resources_professions', '', '', true); $query .= $conditionAll . " " . getOrderBy('profession', $columns); $result = $DB->query($query);
} if ($item->isField('locations_id')) { $select .= ", `glpi_locations`.`completename` AS location\n , `glpi_locations`.`building`\n , `glpi_locations`.`room`"; $from .= "LEFT JOIN `glpi_locations`\n ON (`glpi_locations`.`id` = `{$table}`.`locations_id`)"; } else { $select .= ", '' AS location, '' AS building, '' AS room"; } $select .= ", `glpi_infocoms`.*\n , `glpi_infocoms`.`suppliers_id` AS supplier\n , `glpi_budgets`.`name` AS budget"; $from .= "LEFT JOIN `glpi_infocoms`\n ON (`glpi_infocoms`.`itemtype` = '{$itemtype}'\n AND `glpi_infocoms`.`items_id` = `{$table}`.`id`)\n LEFT JOIN `glpi_budgets`\n ON (`glpi_budgets`.`id` = `glpi_infocoms`.`budgets_id`)"; if ($item->maybeDeleted()) { $where .= " AND `{$table}`.`is_deleted` = 0 "; } if ($item->maybeTemplate()) { $where .= " AND `{$table}`.`is_template` = 0 "; } if ($item->isEntityAssign()) { $where .= getEntitiesRestrictRequest(" AND ", $table); } $where .= $budg->getSqlCriteriasRestriction(); $where .= $date->getSqlCriteriasRestriction(); if ($sql) { $sql .= " UNION "; } $sql .= "({$select} {$from} {$where})"; } $report->setGroupBy('entity'); $report->setSqlRequest($sql); $report->execute(); } else { Html::footer(); }
$date = new PluginReportsDateIntervalCriteria($report, 'buy_date'); $type = new PluginReportsItemTypeCriteria($report, 'itemtype', '', 'infocom_types'); $budg = new PluginReportsDropdownCriteria($report, 'budgets_id', 'glpi_budgets', __('Budget')); //Display criterias form is needed $report->displayCriteriasForm(); $display_type = Search::HTML_OUTPUT; //If criterias have been validated if ($report->criteriasValidated()) { $report->setSubNameAuto(); $title = $report->getFullTitle(); $itemtype = $type->getParameterValue(); if ($itemtype && $itemtype != "all") { $types = array($itemtype); } else { $types = array(); $sql = "SELECT DISTINCT `itemtype`\n FROM `glpi_infocoms` " . getEntitiesRestrictRequest('WHERE', 'glpi_infocoms') . $date->getSqlCriteriasRestriction('AND') . $budg->getSqlCriteriasRestriction('AND'); foreach ($DB->request($sql) as $data) { $types[] = $data['itemtype']; } } $result = array(); foreach ($types as $type) { if (!class_exists($type)) { continue; } $item = new $type(); $table = $item->getTable(); $sql = "SELECT COUNT(*) AS cpt\n FROM `{$table}`\n INNER JOIN `glpi_infocoms` ON (`glpi_infocoms`.`itemtype`='{$type}'\n AND `glpi_infocoms`.`items_id`=`{$table}`.`id`)" . getEntitiesRestrictRequest('WHERE', $table); if ($item->maybeDeleted()) { $sql .= " AND NOT `{$table}`.`is_deleted` "; }