*/ /* * ---------------------------------------------------------------------- * Original Author of file: Benoit Machiavello * * Purpose of file: * Generate group members report * ---------------------------------------------------------------------- */ $USEDBREPLICATE = 1; $DBCONNECTION_REQUIRED = 0; include "../../../../inc/includes.php"; //TRANS: The name of the report = Tickets opened at night, sorted by priority $report = new PluginReportsAutoReport(__('statnightticketsbypriority_report_title', 'reports')); //Report's search criterias new PluginReportsDateIntervalCriteria($report, '`glpi_tickets`.`date`', __('Opening date')); $timeInterval = new PluginReportsTimeIntervalCriteria($report, '`glpi_tickets`.`date`'); //Criterias default values $timeInterval->setStartTime($CFG_GLPI['planning_end']); $timeInterval->setEndtime($CFG_GLPI['planning_begin']); //Display criterias form is needed $report->displayCriteriasForm(); //If criterias have been validated if ($report->criteriasValidated()) { $report->setSubNameAuto(); //Names of the columns to be displayed $report->setColumns(array(new PluginReportsColumnMap('priority', __('Priority'), array(), array('sorton' => '`priority`, `date`')), new PluginReportsColumnDateTime('date', __('Opening date'), array('sorton' => '`date`')), new PluginReportsColumn('id2', __('ID')), new PluginReportsColumnLink('id', __('Title'), 'Ticket'), new PluginReportsColumn('groupname', __('Group'), array('sorton' => '`glpi_groups_tickets`.`groups_id`, `date`')))); $query = "SELECT `glpi_tickets`.`priority`, `glpi_tickets`.`date` , `glpi_tickets`.`id`,\n `glpi_tickets`.`id` AS id2, `glpi_groups`.`name` as groupname\n FROM `glpi_tickets`\n LEFT JOIN `glpi_groups_tickets`\n ON (`glpi_groups_tickets`.`tickets_id` = `glpi_tickets`.`id`\n AND `glpi_groups_tickets`.`type` = '" . CommonITILActor::ASSIGN . "')\n LEFT JOIN `glpi_groups` ON (`glpi_groups_tickets`.`groups_id` = `glpi_groups`.`id`)\n WHERE `glpi_tickets`.`status` NOT IN ('solved', 'closed')\n AND NOT `glpi_tickets`.`is_deleted` " . $report->addSqlCriteriasRestriction() . getEntitiesRestrictRequest(' AND ', 'glpi_tickets') . $report->getOrderBy('priority'); $report->setSqlRequest($query); $report->execute(); }
Resources is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. Resources 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 Resources. 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; include "../../../../inc/includes.php"; // Instantiate Report with Name $titre = $LANG['plugin_resources']['resourceemploymentwithlapseprofession']; $report = new PluginReportsAutoReport($titre); // Columns title (optional) $report->setColumns(array(new PluginReportsColumnLink('rank_id', PluginResourcesRank::getTypeName(1), 'PluginResourcesRank', array('sorton' => 'rank_name')), new PluginReportsColumn('rank_code', PluginResourcesRank::getTypeName(1) . " - " . __('Code', 'resources'), array('sorton' => 'rank_code')), new PluginReportsColumnDate('rank_begin_date', PluginResourcesRank::getTypeName(1) . " - " . __('Begin date'), array('sorton' => 'rank_begin_date')), new PluginReportsColumnDate('rank_end_date', PluginResourcesRank::getTypeName(1) . " - " . __('End date'), array('sorton' => 'rank_end_date')), new PluginReportsColumnLink('prof_id', PluginResourcesProfession::getTypeName(1), 'PluginResourcesProfession', array('sorton' => 'prof_name')), new PluginReportsColumn('prof_code', PluginResourcesProfession::getTypeName(1) . " - " . __('Code', 'resources'), array('sorton' => 'prof_code')), new PluginReportsColumnDate('prof_begin_date', PluginResourcesProfession::getTypeName(1) . " - " . __('Begin date'), array('sorton' => 'prof_begin_date')), new PluginReportsColumnDate('prof_end_date', PluginResourcesProfession::getTypeName(1) . " - " . __('End date'), array('sorton' => 'prof_end_date')))); // SQL statement $condition = getEntitiesRestrictRequest('AND', 'glpi_plugin_resources_professions', '', '', true); $date = date("Y-m-d"); //display only leaving resource with active employment $query = "SELECT `glpi_plugin_resources_ranks`.`id` as rank_id,\n `glpi_plugin_resources_ranks`.`name` as rank_name,\n `glpi_plugin_resources_ranks`.`code` as rank_code,\n `glpi_plugin_resources_ranks`.`begin_date` as rank_begin_date,\n `glpi_plugin_resources_ranks`.`end_date` as rank_end_date,\n `glpi_plugin_resources_professions`.`id` as prof_id,\n `glpi_plugin_resources_professions`.`name` AS prof_name,\n `glpi_plugin_resources_professions`.`code` AS prof_code,\n `glpi_plugin_resources_professions`.`begin_date` AS prof_begin_date,\n `glpi_plugin_resources_professions`.`end_date` AS prof_end_date\n FROM `glpi_plugin_resources_ranks`\n LEFT JOIN `glpi_plugin_resources_professions`\n ON (`glpi_plugin_resources_ranks`.`plugin_resources_professions_id` = `glpi_plugin_resources_professions`.`id`\n AND ((`glpi_plugin_resources_professions`.`begin_date` IS NULL)\n OR (`glpi_plugin_resources_professions`.`begin_date` < '" . $date . "')\n AND (`glpi_plugin_resources_professions`.`end_date` IS NULL)\n OR (`glpi_plugin_resources_professions`.`end_date` > '" . $date . "')))\n WHERE (`glpi_plugin_resources_ranks`.`is_active` <> `glpi_plugin_resources_professions`.`is_active`)\n OR (`glpi_plugin_resources_ranks`.`begin_date` > `glpi_plugin_resources_professions`.`end_date`)\n OR (`glpi_plugin_resources_ranks`.`end_date` < `glpi_plugin_resources_professions`.`begin_date`)\n OR (`glpi_plugin_resources_ranks`.`end_date` > `glpi_plugin_resources_professions`.`end_date`)\n OR (`glpi_plugin_resources_ranks`.`begin_date` < `glpi_plugin_resources_professions`.`begin_date`)\n OR (`glpi_plugin_resources_ranks`.`end_date` IS NULL AND `glpi_plugin_resources_professions`.`end_date` IS NOT NULL)\n OR (`glpi_plugin_resources_ranks`.`end_date` IS NOT NULL AND `glpi_plugin_resources_professions`.`end_date` IS NULL)\n " . $condition . $report->getOrderBy('rank_id'); $report->setSqlRequest($query); $report->execute();
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(); }
This file is part of Resources. Resources is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. Resources 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 Resources. 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; include "../../../../inc/includes.php"; // Instantiate Report with Name $titre = $LANG['plugin_resources']['resourceemploymentdiff']; $report = new PluginReportsAutoReport($titre); // Columns title (optional) $report->setColumns(array(new PluginReportsColumn('registration_number', __('Administrative number'), array('sorton' => 'registration_number')), new PluginReportsColumnLink('resource_id', __('Name'), 'PluginResourcesResource', array('sorton' => 'resource_name')), new PluginReportsColumn('firstname', __('First name'), array('sorton' => 'firstname')), new PluginReportsColumnInteger('quota', __('Quota', 'resources'), array('sorton' => 'quota')), new PluginReportsColumn('resource_rank', __('Resource', 'resources') . " - " . PluginResourcesRank::getTypeName(1), array('sorton' => 'resource_rank')), new PluginReportsColumn('resource_profession', __('Resource', 'resources') . " - " . PluginResourcesProfession::getTypeName(1), array('sorton' => 'resource_profession')), new PluginReportsColumn('resource_professionline', __('Resource', 'resources') . " - " . PluginResourcesProfessionLine::getTypeName(1), array('sorton' => 'resource_professionline')), new PluginReportsColumn('resource_professioncategory', __('Resource', 'resources') . " - " . PluginResourcesProfessionCategory::getTypeName(1), array('sorton' => 'resource_professioncategory')), new PluginReportsColumnLink('employment_id', __('Name') . " - " . PluginResourcesEmployment::getTypeName(1), 'PluginResourcesEmployment', array('sorton' => 'employment_name')), new PluginReportsColumnFloat('ratio_employment_budget', __('Ratio Employment / Budget', 'resources'), array('sorton' => 'ratio_employment_budget')), new PluginReportsColumn('employment_rank', PluginResourcesEmployment::getTypeName(1) . " - " . PluginResourcesRank::getTypeName(1), array('sorton' => 'employment_rank')), new PluginReportsColumn('employment_profession', PluginResourcesEmployment::getTypeName(1) . " - " . PluginResourcesProfession::getTypeName(1), array('sorton' => 'employment_profession')), new PluginReportsColumn('employment_professionline', PluginResourcesEmployment::getTypeName(1) . " - " . PluginResourcesProfessionLine::getTypeName(1), array('sorton' => 'employment_professionline')), new PluginReportsColumn('employment_professioncategory', PluginResourcesEmployment::getTypeName(1) . " - " . PluginResourcesProfessionCategory::getTypeName(1), array('sorton' => 'employment_professioncategory')), new PluginReportsColumnDate('begin_date', __('Begin date'), array('sorton' => 'begin_date')), new PluginReportsColumnDate('end_date', __('End date'), array('sorton' => 'end_date')), new PluginReportsColumn('employment_state', PluginResourcesEmploymentState::getTypeName(1), array('sorton' => 'employment_state')), new PluginReportsColumn('employer_name', __('Name') . " - " . PluginResourcesEmployer::getTypeName(1), array('sorton' => 'employer_name')))); // SQL statement $condition = getEntitiesRestrictRequest(' AND ', "glpi_plugin_resources_employments", '', '', false); //display only resource which have rank and profession not equal to employment rank or profession $query = "SELECT `glpi_users`.`registration_number`,\n `glpi_users`.`id` as user_id,\n `glpi_plugin_resources_resources`.`id` as resource_id,\n `glpi_plugin_resources_resources`.`name` as resource_name,\n `glpi_plugin_resources_resources`.`firstname`,\n `glpi_plugin_resources_resources`.`quota`,\n `glpi_plugin_resources_ranks`.`name` AS resource_rank,\n `glpi_plugin_resources_professions`.`name` AS resource_profession,\n `glpi_plugin_resources_professionlines`.`name` AS resource_professionline,\n `glpi_plugin_resources_professioncategories`.`name` AS resource_professioncategory,\n `glpi_plugin_resources_employments`.`name` AS employment_name,\n `glpi_plugin_resources_employments`.`id` AS employment_id,\n `glpi_plugin_resources_employments`.`ratio_employment_budget`,\n `glpi_plugin_resources_employmentranks`.`name` AS employment_rank,\n `glpi_plugin_resources_employmentprofessions`.`name` AS employment_profession,\n `glpi_plugin_resources_employmentprofessionlines`.`name` AS employment_professionline,\n `glpi_plugin_resources_employmentprofessioncategories`.`name` AS employment_professioncategory,\n `glpi_plugin_resources_employments`.`begin_date`,\n `glpi_plugin_resources_employments`.`end_date`,\n `glpi_plugin_resources_employmentstates`.`name` AS employment_state,\n `glpi_plugin_resources_employers`.`name` AS employer_name\n FROM `glpi_users`\n LEFT JOIN `glpi_plugin_resources_resources_items`\n ON (`glpi_users`.`id` = `glpi_plugin_resources_resources_items`.`items_id`\n AND `glpi_plugin_resources_resources_items`.`itemtype`= 'User')\n LEFT JOIN `glpi_plugin_resources_resources`\n ON (`glpi_plugin_resources_resources`.`id` = `glpi_plugin_resources_resources_items`.`plugin_resources_resources_id`)\n LEFT JOIN `glpi_plugin_resources_employments`\n ON (`glpi_plugin_resources_resources`.`id` = `glpi_plugin_resources_employments`.`plugin_resources_resources_id` )\n LEFT JOIN `glpi_plugin_resources_ranks`\n ON (`glpi_plugin_resources_resources`.`plugin_resources_ranks_id` = `glpi_plugin_resources_ranks`.`id`)\n LEFT JOIN `glpi_plugin_resources_professions`\n ON (`glpi_plugin_resources_ranks`.`plugin_resources_professions_id` = `glpi_plugin_resources_professions`.`id`)\n LEFT JOIN `glpi_plugin_resources_professions` AS `glpi_plugin_resources_employmentprofessions`\n ON (`glpi_plugin_resources_employments`.`plugin_resources_professions_id` = `glpi_plugin_resources_employmentprofessions`.`id`)\n LEFT JOIN `glpi_plugin_resources_employers`\n ON (`glpi_plugin_resources_employments`.`plugin_resources_employers_id` = `glpi_plugin_resources_employers`.`id`)\n LEFT JOIN `glpi_plugin_resources_professionlines`\n ON (`glpi_plugin_resources_professions`.`plugin_resources_professionlines_id` = `glpi_plugin_resources_professionlines`.`id`)\n LEFT JOIN `glpi_plugin_resources_professioncategories`\n ON (`glpi_plugin_resources_professions`.`plugin_resources_professioncategories_id` = `glpi_plugin_resources_professioncategories`.`id`)\n LEFT JOIN `glpi_plugin_resources_ranks` AS `glpi_plugin_resources_employmentranks`\n ON (`glpi_plugin_resources_employments`.`plugin_resources_ranks_id` = `glpi_plugin_resources_employmentranks`.`id`)\n LEFT JOIN `glpi_plugin_resources_professionlines` AS `glpi_plugin_resources_employmentprofessionlines`\n ON (`glpi_plugin_resources_employmentprofessions`.`plugin_resources_professionlines_id` = `glpi_plugin_resources_employmentprofessionlines`.`id`)\n LEFT JOIN `glpi_plugin_resources_professioncategories` AS `glpi_plugin_resources_employmentprofessioncategories`\n ON (`glpi_plugin_resources_employmentprofessions`.`plugin_resources_professioncategories_id` = `glpi_plugin_resources_employmentprofessioncategories`.`id`)\n LEFT JOIN `glpi_plugin_resources_employmentstates`\n ON (`glpi_plugin_resources_employments`.`plugin_resources_employmentstates_id` = `glpi_plugin_resources_employmentstates`.`id`)\n WHERE (`glpi_plugin_resources_resources`.`is_leaving` = 0\n AND `glpi_users`.`is_active` = 1\n AND `glpi_plugin_resources_employments`.`plugin_resources_resources_id` <> 0\n AND `glpi_plugin_resources_resources`.`is_deleted` = '0'\n AND `glpi_plugin_resources_resources`.`is_template` = '0'\n " . $condition . " )\n GROUP BY `glpi_plugin_resources_employments`.`id`, `glpi_users`.`id`\n HAVING (resource_profession <> employment_profession\n OR resource_rank <> employment_rank)" . $report->getOrderBy('registration_number'); $report->setSqlRequest($query); $report->execute();
it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. Resources 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 Resources. 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 = 0; include "../../../../inc/includes.php"; //"Rapport listant les ressources sans emploi"; //"Report listing resource without employment"; // Instantiate Report with Name $titre = $LANG['plugin_resources']['resourcewithoutemployment']; $report = new PluginReportsAutoReport($titre); // Columns title (optional) $report->setColumns(array(new PluginReportsColumnInteger('registration_number', __('Administrative number'), array('sorton' => 'registration_number')), new PluginReportsColumnLink('resource_id', __('Name'), 'PluginResourcesResource', array('sorton' => 'resource_name')), new PluginReportsColumn('firstname', __('First name'), array('sorton' => 'firstname')), new PluginReportsColumn('rank', PluginResourcesRank::getTypeName(1), array('sorton' => 'rank')), new PluginReportsColumn('situation', PluginResourcesResourceSituation::getTypeName(1), array('sorton' => 'situation')), new PluginReportsColumn('state', PluginResourcesResourceState::getTypeName(1), array('sorton' => 'state')), new PluginReportsColumnDate('date_begin', __('Arrival date', 'resources'), array('sorton' => 'date_begin')), new PluginReportsColumnDate('date_end', __('Departure date', 'resources'), array('sorton' => 'date_end')))); // SQL statement $condition = getEntitiesRestrictRequest(' AND ', "glpi_plugin_resources_resources", '', '', false); $date = date("Y-m-d"); //display only resource without user linked $query = "SELECT `glpi_users`.`registration_number`,\n `glpi_users`.`id` as user_id,\n `glpi_plugin_resources_resources`.`id` as resource_id,\n `glpi_plugin_resources_resources`.`name` as resource_name,\n `glpi_plugin_resources_resources`.`firstname`,\n `glpi_plugin_resources_ranks`.`name` AS rank,\n `glpi_plugin_resources_resourcesituations`.`name` AS situation,\n `glpi_plugin_resources_resourcestates`.`name` AS state,\n `glpi_plugin_resources_resources`.`date_begin`,\n `glpi_plugin_resources_resources`.`date_end`\n FROM `glpi_users`\n LEFT JOIN `glpi_plugin_resources_resources_items`\n ON (`glpi_users`.`id` = `glpi_plugin_resources_resources_items`.`items_id`\n AND `glpi_plugin_resources_resources_items`.`itemtype`= 'User')\n LEFT JOIN `glpi_plugin_resources_resources`\n ON (`glpi_plugin_resources_resources`.`id` = `glpi_plugin_resources_resources_items`.`plugin_resources_resources_id`)\n LEFT JOIN `glpi_plugin_resources_ranks`\n ON (`glpi_plugin_resources_resources`.`plugin_resources_ranks_id` = `glpi_plugin_resources_ranks`.`id`)\n LEFT JOIN `glpi_plugin_resources_resourcesituations`\n ON (`glpi_plugin_resources_resources`.`plugin_resources_resourcesituations_id` = `glpi_plugin_resources_resourcesituations`.`id`)\n LEFT JOIN `glpi_plugin_resources_resourcestates`\n ON (`glpi_plugin_resources_resources`.`plugin_resources_resourcestates_id` = `glpi_plugin_resources_resourcestates`.`id`)\n WHERE (`glpi_plugin_resources_resources`.`is_leaving` = 0\n AND `glpi_users`.`is_active` = 1\n AND `glpi_plugin_resources_resources`.`is_deleted` = '0'\n AND `glpi_plugin_resources_resources`.`is_template` = '0'\n AND `glpi_plugin_resources_resources`.`id` NOT IN\n (SELECT DISTINCT(`plugin_resources_resources_id`)\n FROM `glpi_plugin_resources_employments`\n WHERE ((`glpi_plugin_resources_employments`.`end_date` IS NULL )\n OR (`glpi_plugin_resources_employments`.`end_date` > '" . $date . "' ))\n AND ((`glpi_plugin_resources_employments`.`begin_date` IS NULL)\n OR ( `glpi_plugin_resources_employments`.`begin_date` < '" . $date . "')))\n " . $condition . ")\n AND ((`glpi_plugin_resources_resources`.`date_end` IS NULL )\n OR (`glpi_plugin_resources_resources`.`date_end` > '" . $date . "' ))\n AND ((`glpi_plugin_resources_resources`.`date_begin` IS NULL)\n OR ( `glpi_plugin_resources_resources`.`date_begin` < '" . $date . "'))" . $report->getOrderBy('resource_id'); $report->setSqlRequest($query); $report->execute();
it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. Resources 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 Resources. 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 = 0; include "../../../../inc/includes.php"; //"Rapport listant les ressources sans utilisateurs"; //"Report listing resource without user"; // Instantiate Report with Name $titre = $LANG['plugin_resources']['resourcewithoutuser']; $report = new PluginReportsAutoReport($titre); // Columns title (optional) $report->setColumns(array(new PluginReportsColumnLink('resource_id', __('Name'), 'PluginResourcesResource', array('sorton' => 'resource_name')), new PluginReportsColumn('firstname', __('First name'), array('sorton' => 'firstname')), new PluginReportsColumn('rank', PluginResourcesRank::getTypeName(1), array('sorton' => 'rank')), new PluginReportsColumn('situation', PluginResourcesResourceSituation::getTypeName(1), array('sorton' => 'situation')), new PluginReportsColumn('state', PluginResourcesResourceState::getTypeName(1), array('sorton' => 'state')), new PluginReportsColumnDate('date_begin', __('Arrival date', 'resources'), array('sorton' => 'date_begin')), new PluginReportsColumnDate('date_end', __('Departure date', 'resources'), array('sorton' => 'date_end')))); // SQL statement $condition = getEntitiesRestrictRequest(' AND ', "glpi_plugin_resources_resources", '', '', false); $date = date("Y-m-d"); //display only resource without user linked $query = "SELECT `glpi_plugin_resources_resources`.`id` as resource_id,\n `glpi_plugin_resources_resources`.`name` as resource_name,\n `glpi_plugin_resources_resources`.`firstname`,\n `glpi_plugin_resources_ranks`.`name` AS rank,\n `glpi_plugin_resources_resourcesituations`.`name` AS situation,\n `glpi_plugin_resources_resourcestates`.`name` AS state,\n `glpi_plugin_resources_resources`.`date_begin`,\n `glpi_plugin_resources_resources`.`date_end`\n FROM `glpi_plugin_resources_resources`\n LEFT JOIN `glpi_plugin_resources_ranks`\n ON (`glpi_plugin_resources_resources`.`plugin_resources_ranks_id` = `glpi_plugin_resources_ranks`.`id`)\n LEFT JOIN `glpi_plugin_resources_resourcesituations`\n ON (`glpi_plugin_resources_resources`.`plugin_resources_resourcesituations_id` = `glpi_plugin_resources_resourcesituations`.`id`)\n LEFT JOIN `glpi_plugin_resources_resourcestates`\n ON (`glpi_plugin_resources_resources`.`plugin_resources_resourcestates_id` = `glpi_plugin_resources_resourcestates`.`id`)\n WHERE (`glpi_plugin_resources_resources`.`is_leaving` = 0\n AND `glpi_plugin_resources_resources`.`id` NOT IN (SELECT `plugin_resources_resources_id`\n FROM `glpi_plugin_resources_resources_items`\n WHERE `glpi_plugin_resources_resources_items`.`itemtype`= 'User')\n AND `glpi_plugin_resources_resources`.`is_deleted` = '0'\n AND `glpi_plugin_resources_resources`.`is_template` = '0'\n " . $condition . ")\n AND ((`glpi_plugin_resources_resources`.`date_end` IS NULL )\n OR (`glpi_plugin_resources_resources`.`date_end` > '" . $date . "' ))\n AND ((`glpi_plugin_resources_resources`.`date_begin` IS NULL)\n OR ( `glpi_plugin_resources_resources`.`date_begin` < '" . $date . "'))" . $report->getOrderBy('resource_id'); $report->setSqlRequest($query); $report->execute();
http://www.gnu.org/licenses/agpl-3.0-standalone.html @link https://forge.indepnet.net/projects/reports @link http://www.glpi-project.org/ @since 2009 -------------------------------------------------------------------------- */ $USEDBREPLICATE = 1; $DBCONNECTION_REQUIRED = 0; include "../../../../inc/includes.php"; //TRANS: The name of the report = Printers $report = new PluginReportsAutoReport(__('printers_report_title', 'reports')); // Definition of the criteria $grpcrit = new PluginReportsGroupCriteria($report, 'glpi_printers.groups_id', '', 'is_itemgroup'); $loccrit = new PluginReportsLocationCriteria($report, 'glpi_printers.locations_id'); //Display criterias form is needed $report->displayCriteriasForm(); //If criterias have been validated if ($report->criteriasValidated()) { $report->setSubNameAuto(); $cols = array(new PluginReportsColumnLink('id', __('Name'), 'Printer', array('with_navigate' => true, 'sorton' => 'glpi_printers.name')), new PluginReportsColumn('state', __('Status')), new PluginReportsColumn('manu', __('Manufacturer')), new PluginReportsColumn('model', __('Model'), array('sorton' => 'glpi_manufacturers.name, glpi_printermodels.name')), new PluginReportsColumn('serial', __('Serial number')), new PluginReportsColumn('otherserial', __('Inventory number')), new PluginReportsColumn('immo_number', __('Immobilization number')), new PluginReportsColumnDate('buy_date', __('Date of purchase'), array('sorton' => 'glpi_infocoms.buy_date')), new PluginReportsColumnDate('use_date', __('Startup date'), array('sorton' => 'glpi_infocoms.use_date')), new PluginReportsColumnInteger('last_pages_counter', __('Printed pages')), new PluginReportsColumnLink('user', __('User'), 'User'), new PluginReportsColumnLink('groupe', __('Group'), 'Group', array('sorton' => 'glpi_groups.name')), new PluginReportsColumnInteger('compgrp', __('Computers in the group', 'reports')), new PluginReportsColumnInteger('usergrp', __('Users in the group', 'reports')), new PluginReportsColumnLink('location', __('Location'), 'Location', array('sorton' => 'glpi_locations.completename')), new PluginReportsColumnInteger('comploc', __('Computers in the location', 'reports')), new PluginReportsColumnInteger('userloc', __('Users in the location', 'reports'))); $report->setColumns($cols); $compgrp = "SELECT COUNT(*)\n FROM `glpi_computers`\n WHERE `glpi_computers`.`groups_id`>0\n AND `glpi_computers`.`groups_id`=`glpi_printers`.`groups_id`"; $usergrp = "SELECT COUNT(*)\n FROM `glpi_groups_users`\n WHERE `glpi_groups_users`.`groups_id`>0\n AND `glpi_groups_users`.`groups_id`=`glpi_printers`.`groups_id`"; $comploc = "SELECT COUNT(*)\n FROM `glpi_computers`\n WHERE `glpi_computers`.`locations_id`>0\n AND `glpi_computers`.`locations_id`=`glpi_printers`.`locations_id`"; $userloc = "SELECT COUNT(*)\n FROM `glpi_users`\n WHERE `glpi_users`.`locations_id`>0\n AND `glpi_users`.`locations_id`=`glpi_printers`.`locations_id`"; $sql = "SELECT `glpi_printers`.`id`, `glpi_printers`.`serial`, `glpi_printers`.`otherserial`,\n `glpi_printers`.`last_pages_counter`,\n `glpi_printermodels`.`name` AS model,\n `glpi_manufacturers`.`name` AS manu,\n `glpi_printers`.`users_id` AS user,\n `glpi_printers`.`groups_id` AS groupe,\n (" . $compgrp . ") AS compgrp,\n (" . $usergrp . ") AS usergrp,\n `glpi_locations`.`id` AS location,\n (" . $comploc . ") AS comploc,\n (" . $userloc . ") AS userloc,\n `glpi_infocoms`.`immo_number`, `glpi_infocoms`.`buy_date`,\n `glpi_infocoms`.`use_date`,\n `glpi_states`.`name` AS state\n FROM `glpi_printers`\n LEFT JOIN `glpi_printermodels`\n ON (`glpi_printermodels`.`id`=`glpi_printers`.`printermodels_id`)\n LEFT JOIN `glpi_manufacturers`\n ON (`glpi_manufacturers`.`id`=`glpi_printers`.`manufacturers_id`)\n LEFT JOIN `glpi_states` ON (`glpi_states`.`id`=`glpi_printers`.`states_id`)\n LEFT JOIN `glpi_infocoms` ON (`glpi_infocoms`.`itemtype`='Printer'\n AND `glpi_infocoms`.`items_id`=`glpi_printers`.`id`)\n LEFT JOIN `glpi_locations` ON (`glpi_locations`.`id`=`glpi_printers`.`locations_id`)\n LEFT JOIN `glpi_groups` ON (`glpi_groups`.`id`=`glpi_printers`.`groups_id`) " . getEntitiesRestrictRequest('WHERE', 'glpi_printers') . $report->addSqlCriteriasRestriction() . $report->getOrderBy('groupe'); $report->setSqlRequest($sql); $report->execute(); } 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 Affero General Public License for more details. You should have received a copy of the GNU Affero General Public License along with Reports. If not, see <http://www.gnu.org/licenses/>. @package reports @authors Nelly Mahu-Lasson, Remi Collet @copyright Copyright (c) 2009-2015 Reports plugin team @license AGPL License 3.0 or (at your option) any later version http://www.gnu.org/licenses/agpl-3.0-standalone.html @link https://forge.indepnet.net/projects/reports @link http://www.glpi-project.org/ @since 2009 -------------------------------------------------------------------------- */ $USEDBREPLICATE = 1; $DBCONNECTION_REQUIRED = 0; // not really a big SQL request include "../../../../inc/includes.php"; //TRANS: The name of the report = Location tree $report = new PluginReportsAutoReport(__('location_report_title', 'reports')); $report->setColumns(array(new PluginReportsColumn('entity', __('Entity'), array('sorton' => 'entity,location')), new PluginReportsColumn('location', __('Location'), array('sorton' => 'location')), new PluginReportsColumnLink('link', _n('Link', 'Links', 2), 'Location', array('sorton' => '`glpi_locations`.`name`')))); // SQL statement $query = "SELECT `glpi_entities`.`completename` AS entity,\n `glpi_locations`.`completename` AS location,\n `glpi_locations`.`id` AS link\n FROM `glpi_locations`\n LEFT JOIN `glpi_entities` ON (`glpi_locations`.`entities_id` = `glpi_entities`.`id`)" . getEntitiesRestrictRequest(" WHERE ", "glpi_locations") . $report->getOrderBy('entity'); $report->setGroupBy('entity'); $report->setSqlRequest($query); $report->execute();
Resources is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. Resources 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 Resources. 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 = 0; include "../../../../inc/includes.php"; //"Rapport listant les ressources partantes ayant des emplois actifs" //"Report listing resource leaving with employment active"; // Instantiate Report with Name $titre = $LANG['plugin_resources']['resourceleavingwithactiveemployment']; $report = new PluginReportsAutoReport($titre); // Columns title (optional), from $LANG $report->setColumns(array(new PluginReportsColumnInteger('registration_number', __('Administrative number'), array('sorton' => 'registration_number')), new PluginReportsColumnLink('resource_id', __('Name'), 'PluginResourcesResource', array('sorton' => 'resource_name')), new PluginReportsColumn('firstname', __('First name'), array('sorton' => 'firstname')), new PluginReportsColumn('resource_rank', PluginResourcesRank::getTypeName(1), array('sorton' => 'resource_rank')), new PluginReportsColumn('resources_situation', PluginResourcesResourceSituation::getTypeName(1), array('sorton' => 'resources_situation')), new PluginReportsColumn('resource_state', PluginResourcesResourceState::getTypeName(1), array('sorton' => 'resource_state')), new PluginReportsColumnDate('date_begin', __('Arrival date', 'resources'), array('sorton' => 'date_begin')), new PluginReportsColumnDate('date_end', __('Departure date', 'resources'), array('sorton' => 'date_end')), new PluginReportsColumnLink('employment_id', __('Name') . " - " . _n('Employment', 'Employments', 1, 'resources'), 'PluginResourcesEmployment', array('sorton' => 'employment_name')), new PluginReportsColumn('employment_profession', _n('Employment', 'Employments', 1, 'resources') . " - " . _n('Profession', 'Professions', 2, 'resources'), array('sorton' => 'employment_profession')), new PluginReportsColumn('employment_state', _n('Employment state', 'Employment states', 1, 'resources'), array('sorton' => 'employment_state')), new PluginReportsColumn('employer_name', __('Name') . " - " . _n('Employer', 'Employers', 1, 'resources'), array('sorton' => 'employer_name')))); // SQL statement $condition = getEntitiesRestrictRequest(' AND ', "glpi_plugin_resources_resources", '', '', false); //display only leaving resource with active employment $query = "SELECT `glpi_users`.`registration_number`,\n `glpi_users`.`id` as user_id,\n `glpi_plugin_resources_resources`.`id` as resource_id,\n `glpi_plugin_resources_resources`.`name` as resource_name,\n `glpi_plugin_resources_resources`.`firstname`,\n `glpi_plugin_resources_ranks`.`name` AS resource_rank,\n `glpi_plugin_resources_resourcesituations`.`name` AS resources_situation,\n `glpi_plugin_resources_resourcestates`.`name` AS resource_state,\n `glpi_plugin_resources_resources`.`date_begin`,\n `glpi_plugin_resources_resources`.`date_end`,\n `glpi_plugin_resources_employments`.`name` AS employment_name,\n `glpi_plugin_resources_employments`.`id` AS employment_id,\n `glpi_plugin_resources_employmentprofessions`.`name` AS employment_profession,\n `glpi_plugin_resources_employmentstates`.`name` AS employment_state,\n `glpi_plugin_resources_employers`.`completename` AS employer_name\n FROM `glpi_users`\n LEFT JOIN `glpi_plugin_resources_resources_items`\n ON (`glpi_users`.`id` = `glpi_plugin_resources_resources_items`.`items_id`\n AND `glpi_plugin_resources_resources_items`.`itemtype`= 'User')\n LEFT JOIN `glpi_plugin_resources_resources`\n ON (`glpi_plugin_resources_resources`.`id` = `glpi_plugin_resources_resources_items`.`plugin_resources_resources_id`)\n LEFT JOIN `glpi_plugin_resources_resourcesituations`\n ON (`glpi_plugin_resources_resources`.`plugin_resources_resourcesituations_id` = `glpi_plugin_resources_resourcesituations`.`id`)\n LEFT JOIN `glpi_plugin_resources_resourcestates`\n ON (`glpi_plugin_resources_resources`.`plugin_resources_resourcestates_id` = `glpi_plugin_resources_resourcestates`.`id`)\n LEFT JOIN `glpi_plugin_resources_employments`\n ON (`glpi_plugin_resources_resources`.`id` = `glpi_plugin_resources_employments`.`plugin_resources_resources_id` )\n LEFT JOIN `glpi_plugin_resources_ranks`\n ON (`glpi_plugin_resources_resources`.`plugin_resources_ranks_id` = `glpi_plugin_resources_ranks`.`id`)\n LEFT JOIN `glpi_plugin_resources_professions` AS `glpi_plugin_resources_employmentprofessions`\n ON (`glpi_plugin_resources_employments`.`plugin_resources_professions_id` = `glpi_plugin_resources_employmentprofessions`.`id`)\n LEFT JOIN `glpi_plugin_resources_employers`\n ON (`glpi_plugin_resources_employments`.`plugin_resources_employers_id` = `glpi_plugin_resources_employers`.`id`)\n LEFT JOIN `glpi_plugin_resources_employmentstates`\n ON (`glpi_plugin_resources_employments`.`plugin_resources_employmentstates_id` = `glpi_plugin_resources_employmentstates`.`id`)\n WHERE (`glpi_plugin_resources_resources`.`is_leaving` = 1\n AND `glpi_users`.`is_active` = 1\n AND `glpi_plugin_resources_employments`.`plugin_resources_resources_id` <> 0\n AND `glpi_plugin_resources_resources`.`is_deleted` = '0'\n AND `glpi_plugin_resources_resources`.`is_template` = '0'\n AND `glpi_plugin_resources_employmentstates`.`is_active` = 1\n " . $condition . " )\n GROUP BY `glpi_plugin_resources_employments`.`id`, `glpi_users`.`id`" . $report->getOrderBy('registration_number'); $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(); }