Forum GLPI-Project

claugiral

Member

Registered: 2015-04-13

Posts: 55

Average projects

Hello GLPI Team,

GLPI 0.85.4

I want to get the average of my projects automatically (In the main page of a project, the field percent done), from the tasks added to my project, for that I have the sql query, and I think, I have to put it in the project.class.php file, but I dont know how to do it, my knowledge in php are very poor.

Could somebody help me, telling me how show this consult in the main page?

Here the code I want to introduce

"SELECT SUM( glpi_projecttasks.percent_done /100 * glpi_projecttasks.planned_duration ) / SUM( glpi_projecttasks.planned_duration ) *100
FROM glpi_projecttasks, glpi_projects
WHERE glpi_projects.id = glpi_projecttasks.projects_id
AND `glpi_projecttasks`.`projects_id` = '$ID' ";

Thanks a lot,

Claudia

elliot01

Member

From: England

Registered: 2010-03-23

Posts: 206

Re: Average projects

Hi Claudia,

I'm pretty amateur with PHP myself, but I may be able to help you.

Try this:

1) Make a copy of your project.class.php file.
2) Edit project.class.php and at line 797, insert this:

        function funcAutoPercentDone($ID) {

            global $CFG_GLPI, $DB;

            $vSQL="SELECT SUM( glpi_projecttasks.percent_done /100 * glpi_projecttasks.planned_duration ) / SUM( glpi_projecttasks.planned_duration ) *100 AS 'PERCENTDONE'
                    FROM glpi_projecttasks, glpi_projects
                    WHERE glpi_projects.id = glpi_projecttasks.projects_id
                    AND `glpi_projecttasks`.`projects_id` = ".$ID;
            $vResult        = $DB->query($vSQL);
            $vPercentDone   = $DB->fetch_assoc($vResult);
            $vQueryResults  = $vPercentDone['PERCENTDONE'];
            
            return round($vQueryResults, 1)."%";
            
        }

     
3) Next go down to line to 868 and comment out (or remove) the following:

        Dropdown::showNumber("percent_done", array('value' => $this->fields['percent_done'],
                                                 'min'   => 0,
                                                 'max'   => 100,
                                                 'step'  => 5,
                                                 'unit'  => '%'));

4) Put the following it its place:

      echo funcAutoPercentDone($ID);

This works on my v0.85.4 install, but the result of your SQL doesn't seem to return what I would expect. It looks like you know what you're doing in SQL though, so I'll leave that with you

If you want a copy of my project.class.php with these changes, just reply to this thread.

Hope that helps.

Elliot

---

1500+ Computers / 1100+ users

claugiral

Member

Registered: 2015-04-13

Posts: 55

Re: Average projects

Thank you very much elliot!

I design a trigger and only change the presentation in the php, I dont know what would be most usefull and better for the performance of the glpi, this is my trigger, I hope any of two ways can be used for improve the future versions of glpi.

This is my trigger:
delimiter $$
CREATE TRIGGER before_glpi_projecttasks_update
    AFTER UPDATE ON glpi_projecttasks
    FOR EACH ROW BEGIN
   
    declare cc int;
   
    select (sum(ifnull(percent_done,0)/100 * ifnull(planned_duration,0))/sum(ifnull(planned_duration,0)))*100
    into cc
    from glpi_projecttasks
    where projects_id=OLD.projects_id;
   
    update glpi_projects
    set percent_done= cc
    where id=OLD.projects_id;
   
END$$
delimiter ;

With this two options I close this topic
[resolved]

Best regards,

Claudia

elliot01

Member

From: England

Registered: 2010-03-23

Posts: 206

Re: Average projects

For anybody following this thread, I decided to alter Claudia's SQL to suit our own needs.

I am using the following to provide a project done % based on the quantity of project tasks and their respective %done:

$vSQL="SELECT SUM( glpi_projecttasks.percent_done ) / ( SELECT COUNT(*) FROM glpi_projecttasks WHERE projects_id = ".$ID." ) AS 'PERCENTDONE'
FROM glpi_projecttasks
WHERE glpi_projecttasks.projects_id = ".$ID;

Last edited by elliot01 (2015-08-09 22:34:45)

---

1500+ Computers / 1100+ users

elliot01

Member

From: England

Registered: 2010-03-23

Posts: 206

Re: Average projects

Funtion should be changed to the following, to handle new projects, and projects without any sub-tasks:

        function funcAutoPercentDone($ID) {

            global $CFG_GLPI, $DB;

            $vSQL="SELECT SUM( glpi_projecttasks.percent_done ) / ( SELECT COUNT(*) FROM glpi_projecttasks WHERE projects_id = ".$ID." ) AS 'PERCENTDONE'
                FROM glpi_projecttasks
                WHERE glpi_projecttasks.projects_id = ".$ID;
            if ($vResult        = $DB->query($vSQL) ) {
                $vPercentDone   = $DB->fetch_assoc($vResult);
                $vQueryResults  = $vPercentDone['PERCENTDONE'];
            }
            else { $vQueryResults = 0; }
            
            return round($vQueryResults, 1)."%";
            
        }

Last edited by elliot01 (2015-08-12 13:50:35)

---

1500+ Computers / 1100+ users