-------------------------------------------------------------------------- */ $USEDBREPLICATE = 0; $DBCONNECTION_REQUIRED = 0; include "../../../../inc/includes.php"; //TRANS: The name of the report = Users with no right $report = new PluginReportsAutoReport(__('zombies_report_title', 'reports')); $name = new PluginReportsTextCriteria($report, 'name', __('Login')); $tab = array(0 => __('No'), 1 => __('Yes')); $filter = new PluginReportsArrayCriteria($report, 'tickets', __('With no ticket', 'reports'), $tab); //Display criterias form is needed $report->displayCriteriasForm(); //If criterias have been validated if ($report->criteriasValidated()) { $report->setSubNameAuto(); $report->delCriteria('tickets'); $cols = array(new PluginReportsColumnItemCheckbox('id', 'User'), new PluginReportsColumnLink('id2', __('User'), 'User', array('with_comment' => true, 'with_navigate' => true)), new PluginReportsColumn('name', __('Login'), array('sorton' => 'name')), new PluginReportsColumn('email', __('Email')), new PluginReportsColumn('phone', __('Phone')), new PluginReportsColumn('location', __('Location')), new PluginReportsColumnDate('last_login', __('Last login'), array('sorton' => 'last_login'))); if (!$filter->getParameterValue()) { $cols[] = new PluginReportsColumnInteger('nb1', __('Writer'), array('with_zero' => false, 'sorton' => 'nb1')); $cols[] = new PluginReportsColumnInteger('nb2', __('Requester'), array('with_zero' => false, 'sorton' => 'nb2')); $cols[] = new PluginReportsColumnInteger('nb3', __('Watcher'), array('with_zero' => false, 'sorton' => 'nb3')); $cols[] = new PluginReportsColumnInteger('nb4', __('Technician'), array('with_zero' => false, 'sorton' => 'nb4')); } $report->setColumns($cols); $query = "SELECT `glpi_users`.`id`, `glpi_users`.`id` AS id2, `glpi_users`.`name`, `last_login`,\n (SELECT COUNT(*)\n FROM `glpi_tickets`\n WHERE `glpi_users`.`id` = `glpi_tickets`.`users_id_recipient`\n ) AS nb1,\n (SELECT COUNT(*)\n FROM `glpi_tickets_users`\n WHERE `glpi_users`.`id` = `glpi_tickets_users`.`users_id`\n AND `glpi_tickets_users`.`type`=" . CommonITILActor::REQUESTER . "\n ) AS nb2,\n (SELECT COUNT(*)\n FROM `glpi_tickets_users`\n WHERE `glpi_users`.`id` = `glpi_tickets_users`.`users_id`\n AND `glpi_tickets_users`.`type`=" . CommonITILActor::OBSERVER . "\n ) AS nb3,\n (SELECT COUNT(*)\n FROM `glpi_tickets_users`\n WHERE `glpi_users`.`id` = `glpi_tickets_users`.`users_id`\n AND `glpi_tickets_users`.`type`=" . CommonITILActor::ASSIGN . "\n ) AS nb4,\n `phone`, `glpi_locations`.`completename` as location,\n `glpi_useremails`.`email`\n FROM `glpi_users`\n LEFT JOIN `glpi_locations`\n ON `glpi_locations`.`id` = `glpi_users`.`locations_id`\n LEFT JOIN `glpi_useremails`\n ON `glpi_useremails`.`users_id` = `glpi_users`.`id`\n AND `glpi_useremails`.`is_default`\n WHERE `glpi_users`.`id` NOT IN (\n SELECT distinct `users_id`\n FROM `glpi_profiles_users`\n )\n AND `glpi_users`.`is_deleted`=0 " . $report->addSqlCriteriasRestriction('AND'); if ($filter->getParameterValue()) { $query .= " HAVING nb1=0 AND nb2=0 AND nb3=0 AND nb4=0 "; } $query .= $report->getOrderBy('name'); $report->setSqlRequest($query); $report->execute(array('withmassiveaction' => 'User'));