<?php

$USEDBREPLICATE         = 1;
$DBCONNECTION_REQUIRED  = 0;

include ("../../../../inc/includes.php");

$report = new PluginReportsAutoReport(__('statticketsstatbytasks_report_title', 'reports'));

//Report's search criterias
new PluginReportsDateIntervalCriteria($report, '`glpi_tickettasks`.`date`', __('Tasks date'));
new PluginReportsDropdownCriteria($report, "glpi_entities.id", "glpi_entities", "Entity");

//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 PluginReportsColumnLink('id',"Ticket Name", 'Ticket'),
                             new PluginReportsColumnDateTime('date',"Open date",array('sorton' => '`date`')),
                             new PluginReportsColumn('requester',"Requester"),
                             new PluginReportsColumn('descr',"Ticket descr."),
                             new PluginReportsColumn('solvedate',"Solve date",array('sorton' => '`solvedate`')),
                             new PluginReportsColumn('solution',"Solution"),
                             new PluginReportsColumnTimestamp('actualtime',"Actual time spend",array('sorton' => '`actualtime`'))));

   $query = "SELECT glpi_tickets.id AS id,
                 glpi_tickets.date_creation as date,
                 CONCAT(glpi_users.realname, ' ', glpi_users.firstname) AS requester,
                 glpi_tickets.content as descr,
                 glpi_tickets.solvedate AS solvedate,
                 REPLACE(REPLACE(REPLACE(glpi_tickets.solution,'&lt;p&gt;',''),'&lt;/p&gt;',''),'&lt;br /&gt;','<br>') AS solution,
                 SUM(glpi_tickettasks.actiontime) AS actualtime
             FROM glpi_tickettasks
                 JOIN glpi_tickets ON glpi_tickets.id = glpi_tickettasks.tickets_id
                 JOIN glpi_tickets_users ON glpi_tickets_users.tickets_id = glpi_tickets.id AND glpi_tickets_users.type = 1
                 JOIN glpi_users ON glpi_users.id = glpi_tickets_users.users_id
                 JOIN glpi_entities ON glpi_entities.id = glpi_tickets.entities_id
             WHERE NOT glpi_tickets.is_deleted " .
            $report->addSqlCriteriasRestriction() .
            getEntitiesRestrictRequest(' AND ', 'glpi_tickets') .
            " GROUP BY glpi_tickets.id " .
            $report->getOrderBy('date');

   $report->setSqlRequest($query);
   $report->execute();

} else {
   Html::footer();
}