// $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` ";
}