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();
}
reports is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
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/>.
--------------------------------------------------------------------------
*/
$USEDBREPLICATE = 1;
$DBCONNECTION_REQUIRED = 0;
include "../../../../inc/includes.php";
//TRANS: The name of the report = Applications by locations and versions
$report = new PluginReportsAutoReport(__('applicationsbylocation_report_title', 'reports'));
$softwarecategories = new PluginReportsSoftwareCategoriesCriteria($report, 'softwarecategories', __('Software category'));
$softwarecategories->setSqlField("`glpi_softwarecategories`.`id`");
$software = new PluginReportsSoftwareCriteria($report, 'software', __('Applications', 'reports'));
$software->setSqlField("`glpi_softwares`.`id`");
$statecpt = new PluginReportsStatusCriteria($report, 'statecpt', __('Computer status', 'reports'));
$statecpt->setSqlField("`glpi_computers`.`states_id`");
$location = new PluginReportsLocationCriteria($report, 'location', _n('Location', 'Locations', 2));
$location->setSqlField("`glpi_computers`.`locations_id`");
$report->displayCriteriasForm();
// Form validate and only one software with license
if ($report->criteriasValidated()) {
$report->setSubNameAuto();
$report->setColumns(array(new PluginReportsColumnLink('soft', _n('Software', 'Software', 1), 'Software', array('sorton' => 'soft,version')), new PluginReportsColumnLink('locat', _n('Location', 'Locations', 1), 'Location', array('sorton' => 'glpi_locations.name')), new PluginReportsColumnLink('computer', _n('Computer', 'Computers', 1), 'Computer', array('sorton' => 'glpi_computers.name')), new PluginReportsColumn('statecpt', _n('Status', 'Statuses', 1)), new PluginReportsColumnLink('version', __('Version name'), 'SoftwareVersion'), new PluginReportsColumnLink('user', _n('User', 'Users', 1), 'User', array('sorton' => 'glpi_users.name'))));
$query = "SELECT `glpi_softwareversions`.`softwares_id` AS soft,\n `glpi_softwareversions`.`name` AS software,\n `glpi_locations`.`id` AS locat,\n `glpi_computers`.`id` AS computer,\n `state_ver`.`name` AS statever,\n `state_cpt`.`name` AS statecpt,\n `glpi_locations`.`name` as location,\n `glpi_softwareversions`.`id` AS version,\n `glpi_computers`.`users_id` AS user\n FROM `glpi_softwareversions`\n INNER JOIN `glpi_computers_softwareversions`\n ON (`glpi_computers_softwareversions`.`softwareversions_id` = `glpi_softwareversions`.`id`)\n INNER JOIN `glpi_computers`\n ON (`glpi_computers_softwareversions`.`computers_id` = `glpi_computers`.`id`)\n INNER JOIN `glpi_softwares`\n ON (`glpi_softwares`.`id` = `glpi_softwareversions`.`softwares_id`)\n LEFT JOIN `glpi_softwarecategories`\n ON (`glpi_softwares`.`softwarecategories_id` = `glpi_softwarecategories`.`id`)\n LEFT JOIN `glpi_locations`\n ON (`glpi_locations`.`id` = `glpi_computers`.`locations_id`)\n LEFT JOIN `glpi_states` state_ver\n ON (`state_ver`.`id` = `glpi_softwareversions`.`states_id`)\n LEFT JOIN `glpi_states` state_cpt\n ON (`state_cpt`.`id` = `glpi_computers`.`states_id`) " . getEntitiesRestrictRequest('WHERE', 'glpi_softwareversions') . $report->addSqlCriteriasRestriction() . "ORDER BY soft ASC, locat ASC";
$report->setSqlRequest($query);
$report->execute();
}
$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'));
} else {
Html::Footer();
}
