//   $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);
Exemple #4
0
        }
        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` ";
        }